Percentage of total in a matrix grid for second dimension

[SandroRiz]

I have a matrix grid with the Sales by Country and foreach Country the sales by product category

I want the % of total by Country and that's easy, but obviously when I expand a country the number has no sense...

In the example, I would like to see Seafood is 12,48% of the 26k of Italy

Is such a thing possible?

Thanks

[SandroRiz]

BTW, do you perhaps know why in a new file I have the PLUS sign near every ROW, while in an old pbix (but same, last, version of client) I have the old arrows to expan/collapse all the rows ?

[Anonymous]

EDIT:

Hi

You are removing the filter context only from the product table. You need to do that for your sales table:

    DIVIDE(
        [TotalSales],
        CALCULATE(
            [TotalSales],
            ALL(Sales)
         )
    ) 

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[SandroRiz]

That's better (number now make sense and are related to the gran total), but what I want to achieve is the % respect the parent total...

[Anonymous · Accepted solution]

I see, you can test what dimensions are in scope and remove the concext acordingly.

Try this

Measure = 
IF(AND(ISINSCOPE('Country'[CountryName]),ISINSCOPE(Products[Category])),
DIVIDE(
    [TotalSales],
    CALCULATE(
        TotalSales,
        ALL(Products[Category])

)
),
DIVIDE(
    TotalSales,
    CALCULATE(
        TotalSales,
        ALL('Country'[CountryName])

)
)
    )

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[SandroRiz]

WOW !! only a Kudo is not enough!!  Should be a Superkudo button.

Works like a charm... Thanks a lot!

[Anonymous]

Thanks @SandroRiz 

Glad i could help 😀

[SandroRiz]

oh sorry, I left in the screenshot and I though it was visible.. anyway it is

% of total by country = DIVIDE([TotalSales], CALCULATE([TotalSales], ALL(Countries)))

 TotalSales is SUM(Sales[Amount])

(I know it is better to have ALLSELECTED() in case a slicer with countries was here...)

I attach Schema if it is useful...

[Anonymous]

Please check the edited messagem above