Search code examples
excelpowerpivotpowerquery

How can I get a percentage field with power pivot?


This should be a fairly easy question for Power Pivot users since I'm a newbie. I am trying to do the following. After pivoting a table I get a crosstab table like this

rating         count of id
A              1
B              2
Grand Total    3

You can imagine the original table only has two columns (rating and id) and three rows (1 id for A and two different id's for the B rating). What DAX formula do I have to write in order to create a measure that simply shows

rating         percent of id
A              1/3
B              2/3
Grand Total    3/3

By 1/3 of course I mean 0.3333, I wrote it like that so that it is clear that I simply want that percent of id is the count for each rating divided by the total count. Thank you very much


Solution

  • You need to divide the count for each row by the total count.

    DIVIDE (
        COUNT ( Table1[ID] ),
        CALCULATE ( COUNT ( Table1[ID] ), ALL ( Table1 ) )
    )
    

    For this particular calculation, you don't have to write DAX though. You can just set it in the Value Field Settings.

    Summarize Value By  :  Count
    Show Values As      :  % of Column Total