1

I just started using Power Pivot and Dax. Today I learned the benefits of creating my own measures (counta, etc) vs creating them directly in the pivot table. So I thought I could do the same thing with percent to column total. I know I can easily do this with "Show Values As % of Column Total".

I've visited a few different Microsoft and other websites and still don't quite get it. I used the below and a variation with ALLEXCEPT but neither of these behave dynamically exactly like the "Show Values As > % of Column Total." Dynamically = add any column to Filters or Rows section.

How do you do what I want or is it not possible?

I thought I had it with the below but when I added another filter to the pivot table it did not behave dynamically. I also tried ALLEXCEPT and that works but I would have to write in over a dozen columns.

source: https://community.powerbi.com/t5/Developer/Percent-of-Total/m-p/15258

% Total = [TotalQuantity] / CALCULATE( [TotalQuantity] ,ALL( 'Table' ) )

daniellopez46
  • 257

1 Answers1

1

You should use ALLSELECTED around the field you have on the rows in the pivot table, like this:

% Total = [TotalQuantity] / CALCULATE( [TotalQuantity] ,ALLSELECTED( 'Table'[FieldUsedOnRows] ) )

You can also just put the Table name inside ALLSELECTED, but that would mean the denominator will be the sum of all the cells in the pivot table (across all rows and columns for that measure), and so would be the equivalent of the '% of Grand Total'. This is fine if you have no column headers - you can put multiple fields in the rows and still get '% of column total'. Otherwise, it would be more accurate that you want to create '% of measure by specific field'.

Last year I started and (quickly) stopped a blog about this kind of stuff. But one of the very few posts I wrote was about this particular subject.

https://www.flexyourdata.com/blog/excel-calculate-the-difference-between-percentage-of-column-total-columns/

FlexYourData
  • 7,631