How to automatically change the denominator when go from category to sub-category

[Anonymous]

Could anyone kindly help me on this:

If I have a data set for example:

Category	Sub-category	Sales
Fruit	Apple	23
Fruit	Watermelon	12
Vegetables	Carrot	25
Vegetables	Tomato	35
Drink	Orange juice	21
Drink	Apple juice	12
Drink	Melon juice	16

I plan to add one graph to show percentages, firstly the percentage of sales of each category vs total sales, then if I click to go in to details (the 2-arrow icon on the graph), I can have the percentage of sales of each sub-category vs the total sales of that category, rather than vs the total sales of all category.

Is this possible? Thank you so much!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures

percent =
IF (
    ISINSCOPE ( 'Table'[Category] ),
    SUM ( 'Table'[Sales] ) / CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ) ),
    IF (
        ISINSCOPE ( 'Table'[Sub-category] ),
        SUM ( 'Table'[Sales] )
            / CALCULATE (
                SUM ( 'Table'[Sales] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) )
            ),
        SUM ( 'Table'[Sales] ) / SUM ( 'Table'[Sales] )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much!!! This is exactly what I want to achieve!

[Greg_Deckler]

So like the attached? Table 3, Page 3. I just used Show As Percent of Grand Total. You need to use the single drill down arrow to get percentage of the subcategory for the category.