Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
SandroRiz
Helper I
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.jpg2.jpg

1 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! 🙂

View solution in original post

8 REPLIES 8
SandroRiz
Helper I
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 ?

 

2022-10-17_184541.jpg

jcalheir
Solution Supplier
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é
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

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

 

2022-10-17_115246.jpg

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! 🙂

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

 

Works like a charm... Thanks a lot!

Thanks @SandroRiz 

 

Glad i could help 😀

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...
Screenshot 2022-10-17 114051.jpg

Please check the edited messagem above

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.