topic Re: How to calculate/sum measures in a hierachy/matrix? in DAX Commands and Tips

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

I_got_question — Tue, 22 Mar 2022 20:06:04 GMT

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.

This is what I would expect as a result

These are the two tables representing the source data

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-Roll/m-p/411443#M150 )

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

Cheers

Here's the example data:

Tabelle1

ID	L0	L1	L2	L3	L4	NegIndicator	PosIndicator
1	100	110	111	1		X	X
2	100	110	112	2		X
3	100	120	3			X	X
2	200	210	211	213	2		X
4	200	210	212	4		X	X
5	200	220	5			X	X

Tabelle2

ID	Value
1	50
2	46
3	11
4	43
5	77
6	26
1	-74
2	-39
3	-53
4	-14
5	-72
6	-30

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

I_got_question — Fri, 25 Mar 2022 07:47:12 GMT

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?

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

Anonymous — Fri, 25 Mar 2022 08:07:51 GMT

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.

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

I_got_question — Tue, 29 Mar 2022 07:05:30 GMT

Hi Anonymous ,

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.