Help with DAX measure to show values as percent of grand total

[mstone3]

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!

[v-shex-msft · Accepted 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