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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
I_got_question
Frequent Visitor

How to calculate/sum measures in a hierachy/matrix?

Hi all,

 

as of now I have treid many things and tried to wrap my head around this issue but could not find a way to solve it. 

 

Here's the problem:

I need to calculate the value assigned to an account based on the sign of the value. In case the value is negative, the value is assigned a node and in case it is positive it is assigned to another node. The measure is working fine, but the value on higher hierachy levels is not calculated  as I would expect it. 

 

In this example the value on ID = 2 is calculated correct via the sum_swing measure. But on node 110 the value should be -24 instead of -17 in column sum_swinging as ID 2 carries the value 0.

I_got_question_2-1647965906715.pngI_got_question_1-1647965811870.png

 

This is what I would expect as a result

 

I_got_question_1-1647979527155.png

 

These are the two tables representing the source data

I_got_question_3-1647966715788.png

 

The measure is defined as follows:

sum values = calculate(sum(Tabelle2[Value]))

 

 

sum_swinging = 

VAR sum_ac =

SUM(Tabelle2[Value])

VAR indicator_swing =

OR(ISBLANK(SELECTEDVALUE(Tabelle1[NegIndicator])), ISBLANK(SELECTEDVALUE(Tabelle1[PosIndicator])))

VAR indicator_side =

OR(AND(ISBLANK(SELECTEDVALUE(Tabelle1[NegIndicator])), [sum values] > 0), AND(ISBLANK(SELECTEDVALUE(Tabelle1[PosIndicator])), [sum values] < 0))

Return

IF(indicator_swing, IF(indicator_side, sum_ac, 0), sum_ac)

 

What I did alread:

I found this blogpost (https://www.daxpatterns.com/parent-child-hierarchies/ ) but I do not think this is the right way of doing it. Also I found these two explanations but could not figure out how to do is (https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/ , https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-... )

 

Probably the solution is already close but I just cannot put the pieces together.

 

Cheers

F

 

 

Here's the example data:

 

Tabelle1

IDL0L1L2L3L4NegIndicatorPosIndicator
11001101111 XX
21001101122 X 
31001203  XX
22002102112132 X
42002102124 XX
52002205  XX

 

Tabelle2

IDValue
150
246
311
443
577
626
1-74
2-39
3-53
4-14
5-72
6-30

 

I_got_question_1-1647968597245.png

 

 

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @I_got_question ,

 

Please allow me to ask questions and forgive me if I am wrong.

In your table 2, ID 1 to 6 have duplicate values, so how do you reference them?

For example ID1, if you want to get the value of ID1 in the table, is it 50 or -74?

 

 

Best Regards,

Stephen Tao

 

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

Hi @v-stephen-msft ,

the table above is a simplification of the problem and the data used in the underlying case. The duplicates may be assigned to different timestamps thus I select them via a date slicer.

 

For the resulting matrix the selected values (also the multiple lines for one ID) shall simply be added up.

I_got_question
Frequent Visitor

After some further research and testing I found a solution using  a second measure that uses SUMX and builds a temporary table with the original measure. You can find the original post here:

 

https://finance-bi.com/blog/power-bi-totals-incorrect/ 

 

This is the resulting dax formula:

 

swinging_tempTable2 = 

sumx(

SELECTCOLUMNS(Tabelle1, "L0", [L0], "L1", [L1], "L2", [L2], "L3", [L3], "L4", [L4]), Table_Measures[sum_swinging]

)

 

Although this leads to correct subtotals in the matrix hierarchy the performance is not very good for a larger hierarchy and facts table. Is there any idea on how to improve the measure to get a better performance?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors