1

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

edd
  • 933
  • 2
  • 11
  • 24

1 Answers1

1

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
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Dear Alexis. I already knew about changing the Value Field Settings, but wanted to see how it's done in DAX. I was actually looking more for something with ALLSELECTED instead of ALL (in case I apply a filter), but your answer is good. Thanks – edd Jun 20 '18 at 15:15
  • Yep, you want different versions of `ALL` for different situations. I just gave the basic structure for the calculation. – Alexis Olson Jun 20 '18 at 16:01