Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello - I have a very basic table visual that displays the count of categories, such as:
Product Count
Apples 5
Oranges 6
Pears 8
Total 19
The table is connected to two slicers (e.g region and country). I would like to write a DAX measure that calculates the percent of grand total so that the table would look like this:
Product Count
Apples 26%
Oranges 32%
Pears 42%
Total 100%
I realize there is a simple setting "show values as percent of grand total" that would solve the problem, but I need to make it a DAX measure so I can control for the number of decimal places. The closest I can get is:
Percentage = DIVIDE(COUNTROWS(Table),COUNTROWS(ALL(Table))). But this is not correct because it overrides the slicer settings and I do not want to override the slicer settings.
Thanks much for any assistance out there!
Solved! Go to Solution.
HI @mstone3 ,
You can use following measure formula to calculate percent of current amount percent and filtered total amount:
Percentage = DIVIDE ( CALCULATE ( COUNT ( Table[Product] ), VALUES ( Table[Product] ) ), CALCULATE ( COUNT ( Table[Product] ), ALLSELECTED ( Table ) ) )
Regards,
Xiaoxin Sheng
HI @mstone3 ,
You can use following measure formula to calculate percent of current amount percent and filtered total amount:
Percentage = DIVIDE ( CALCULATE ( COUNT ( Table[Product] ), VALUES ( Table[Product] ) ), CALCULATE ( COUNT ( Table[Product] ), ALLSELECTED ( Table ) ) )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |