cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Percentage of total in a matrix grid for second dimension

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

1 ACCEPTED SOLUTION
Solution Supplier

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é

8 REPLIES 8
Helper I

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 ?

Solution Supplier

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é

Helper I

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...

Solution Supplier

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é

Helper I

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

Works like a charm... Thanks a lot!

Solution Supplier

Thanks @SandroRiz

Helper I

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...
Solution Supplier

Please check the edited messagem above

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors