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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
OliTFD
Regular Visitor

How to calculate total Sum in Matrix visual with complex calculation?

I am stuck with a problem for some time now, and hope to get some help here. 
My data contains these for example:
Bildschirmfoto 2022-12-13 um 22.04.12.jpg

 

"Count" is the number of available positions in our case, "level" the percentage of how much of this position is allready financed. Every Country can have different amounts of positions with different levels in different years.

What I finally want to show is drawn in this dummy (red numbers are calculated by hand an inserted in the screenshot ;-).
The totals should show the level on either country or total perspective, taking in count the part the level has on a row base compared to the sum of count for the total country (or overall on the bottom).

Calculation Dummy.jpg

I manage to get the part for every type (full, part, none), as shown above as "Part of Country". But I have no idea how to calculate the percentage on a higher level, which is the sum of them. Even more complicated (for me): Getting the value for the overall perspective ("Gesamt", german).
Bildschirmfoto 2022-12-13 um 22.34.50.jpg
What can I do to get results like in the dummy? The pbix for this demo can be found here.




1 ACCEPTED SOLUTION
IIPowerBlog
Helper I
Helper I

Hi @OliTFD check out my blog post on this (I had a similar case where I wanted to sum quantities per productid , I am pretty sure you will find it useful). It is based on the SUM() OVER PARTITION BY of SQL , I think you can also use the same logic for your COUNT https://www.iipowerblog.com/post/sum-over-partition-by-in-powerbi 

View solution in original post

6 REPLIES 6
OliTFD
Regular Visitor

@IIPowerBlog  At last your idea gave me the solution! I need to drop the measures, and do the calculations in columns, to finally sum them.

First step: Get the count for the country
Total Count per Country COL = CALCULATE(SUM(Facts[Count]),ALLEXCEPT(Facts,Facts[Group],Facts[Country]))

Second: Calculate the count compared to Country

Count compared to Country COL = DIVIDE(Facts[Count],Facts[Total Count per Country COL])

Third: Multiply with level
Part of Country COL = Facts[Count compared to Country COL]*Facts[Level]
Finally sum this for the country level, same method for the group level. And then adjust the visible column in the matrix to
Level % =
Var Typelevel = Sum(Facts[Level])
Var Countrylevel = Sum(Facts[Part of Country COL])
Var Grouplevel = Sum(Facts[Part of Group COL])
Return

If(
    ISINSCOPE(Facts[type]),Typelevel,
    If(ISINSCOPE(Facts[Country]),Countrylevel,Grouplevel))

This gives the following view:
Bildschirmfoto 2022-12-14 um 19.07.51.jpg
Problem solved (and found a typo in my dummy ;-))
OliTFD
Regular Visitor

@IIPowerBlog thanks, but this does not seem to work. The Calculate(Sum(... needs a column... but I need to sum values of a measure, which depends on another measure.
As far as I see your solution does more or less the same as my "Total Count per Country)-measure from step 1.
Or do I get things wrong? 

IIPowerBlog
Helper I
Helper I

Hi @OliTFD check out my blog post on this (I had a similar case where I wanted to sum quantities per productid , I am pretty sure you will find it useful). It is based on the SUM() OVER PARTITION BY of SQL , I think you can also use the same logic for your COUNT https://www.iipowerblog.com/post/sum-over-partition-by-in-powerbi 

OliTFD
Regular Visitor

@amitchandak , AverageX is not the solution, the calculation is a little bit more complex, see below
@v-rzhou-msft Thanks, this part is not the main problem, but thanks for the advise. To get the problem clearer, I try to explain the calculation with the image below:
Calculation Exp 2.jpg

It is a kind of three-steps-calculation:
- compare the count to the total for country
- multiply this with the level
- sum up this results for the country

The red part is where I am lost ;-). This needs to be the _HAND_CALCULATE-Part.
For the overall total ("Gesamt") it will be more or less the same, just changing step 1 to "compare to total for group".

v-rzhou-msft
Community Support
Community Support

Hi @OliTFD ,

 

According to your statement, I think your want to get subtoatl in country level by another calculate logic. However, I am still confused about your calculate logic for red part in your screenshot. Here I suggest you to try ISINSCOPE() function to achieve your goal. Here I use 1 in hand calculate part.

Level % = 
VAR _HAND_CALCULATE = 1
RETURN
    IF (
        ISINSCOPE ( Facts[Type] ),
        SUM ( Facts[Level] ),
        IF ( ISINSCOPE ( Facts[Country] ), _HAND_CALCULATE )
    )

Result is as below.

RicoZhou_0-1671000759845.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

@OliTFD , for the red one, try a measure like

 

AverageX(Table, [Count]*[Level])

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.