How to make a PowerPivot version of an Excel Pivot Table that uses a calculated percentage field?

[kcmuppet]

Hi

I'm trying to create a PowerPivot version of an Excel 2016 Pivot Table that uses a calculated percentage field.

I have fields in my data table as follows:

[Units] (whole number)
[Ceiling] (whole number)
[Penetration] (decimal number, formatted as percentage, calculated as [Units] divided by [Ceiling]
[City] (text)
[Region] (text)

I have a standard Excel Pivot Table that has rows Region and City, and for values Units, Ceiling and a calculated field called [Estimated Penetration] with the formula =SUM(Units/Ceiling) to show the percentages for each. (I can't use the [Penetration] data from the table because the Pivot Tables don't correctly aggregate percentages).

My question is: how can I show the same figures using Power Pivot (and then use those figures in Power View)? I know it has to do with creating a 'measure' but haven't been able to work it out using the online help and have no experience yet with DAX.

Somebody's kind help would be greatly appreciated!

 

[Matt Allington]

You need to write a grand total measure, then divide the original measure with the grand total.
Total Units = sum(tableName[units])
Grand Total = calculate([Total Units],ALL(tableName))
% of units = divide(Total units], [grand total])

but there is a lot to learn. You may like to read my book - it will help you get good quickly. .

Learn to Write DAX - Excelerator BI

Matt

 

[kcmuppet]

Thanks Matt,

I had stumbled my way to:

% of Units:=CALCULATE(SUM([Units]) / sum([Ceiling]))

...which seems to work like my original calculated field, an as much as it shows the % of Units in the visible row of the pivot. If I've understood what you've written, it will show the % of Units in terms of the proportion of the in the visible row of the pivot relative to the total of the units in the whole underlying data table - is that right?

Thanks

 

[Matt Allington]

correct - i must have misunderstood what you were after. Your formula should work without the CALCULATE
=sum(table[units]) / sum(table[ceiling])

The calculate adds no value in your formula. You should also ALWAYS specify the table name in front of the column name. This makes it easier to identify a measure as opposed to a column in a formula.

 

[kcmuppet]

Thanks