Forum Discussion
Solved
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: "Count" is the number of available positions in our case, "level" the perc...
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
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
Finally sum this for the country level, same method for the group level. And then adjust the visible column in the matrix to
This gives the following view:
Problem solved (and found a typo in my dummy ;-))
Third: Multiply with level
Part of Country COL = Facts[Count compared to Country COL]*Facts[Level]
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:
Problem solved (and found a typo in my dummy ;-))