topic Sum up hierarchy not working (with sample) in DAX Commands and Tips

Sum up hierarchy not working (with sample)

MagikJukas — Tue, 13 Feb 2024 12:57:12 GMT

Hello,

I would like to sum up measure 2 children into measure 3 parent.

Measure 3 should return 1.48, insteaed of 0.83, for the first row.

Below the formulas:

Measure 2 = ABS(CALCULATE([FCST Acc.])-CALCULATE([FCST Acc.],ALLSELECTED(Forecast)))

Measure 3 = SUMX(SUMMARIZE(Forecast,Forecast[Material]),[Measure 2])

Any advice how to change Measure 3?

EDIT: Please find here the sample:

https://drive.google.com/file/d/1S5jlGQEKdiVBrlAbDmp83oY3WiUrS4I9/view?usp=sharing

thanks

Re: Sum up hierarchy not working (with sample)

MagikJukas — Tue, 13 Feb 2024 12:57:57 GMT

Added an the file to the post:
https://drive.google.com/file/d/1S5jlGQEKdiVBrlAbDmp83oY3WiUrS4I9/view?usp=sharing

Re: Sum up hierarchy not working (with sample)

Anonymous — Wed, 14 Feb 2024 07:24:11 GMT

Hi MagikJukas ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Create a calculate table

Table = SUMMARIZE(Forecast,Forecast[Series],Forecast[Material],"M",[Measure 2])

Create many to many relationships between two table

Create measure 3

Measure 3 = CALCULATE( SUMX('Table','Table'[M]), ALLEXCEPT('Table','Table'[Series]) )

Final output

Best regards,

Albert He

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

Re: Sum up hierarchy not working (with sample)

MagikJukas — Wed, 14 Feb 2024 08:13:21 GMT

Hello Anonymous , thank you for your reply.

I see this as an option. However, I wish to find a solution without creating a new table.

Isn't it possible to solve it with a some kind of measure?

thanks

Re: Sum up hierarchy not working (with sample)

Anonymous — Wed, 14 Feb 2024 08:36:57 GMT

Hi MagikJukas ,
Since you're using a MEASURE on top of your matrix, and it doesn't seem feasible to use a MEASURE to get the sum of the children of another MEASURE and display it on the parent, you can apply the steps above.

Best regards,

Albert He

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

Re: Sum up hierarchy not working (with sample)

MagikJukas — Thu, 15 Feb 2024 16:12:18 GMT

ok, although I find it strange it is not possible.

let's see if the community finds an alternative solution.

thanks!

Re: Sum up hierarchy not working (with sample)

talespin — Thu, 15 Feb 2024 17:19:38 GMT

hi MagikJukas

Please see if this is what you want, As the below post said, I wonder why would you do that.

Measure 2 = SWITCH(

TRUE(),

ISINSCOPE(Forecast[Material]), ABS(([FCST Acc.])-CALCULATE([FCST Acc.],REMOVEFILTERS(Forecast[Series],Forecast[Material])))

)

Measure 3 =

VAR _SelSeries = SELECTEDVALUE(Forecast[Series])

VAR _FCSTAccTotal = CALCULATE([FCST Acc.],REMOVEFILTERS(Forecast[Series],Forecast[Material]) )

VAR _Sum = SUMX(VALUES(Forecast[Material]), ABS([FCST Acc.] - _FCSTAccTotal) )

RETURN SWITCH(

TRUE(),

NOT(ISINSCOPE(Forecast[Material])) && ISINSCOPE(Forecast[Series]), _Sum

)

Re: Sum up hierarchy not working (with sample)

MagikJukas — Fri, 16 Feb 2024 13:21:55 GMT

Hello talespin

thanks for your reply.

Indeed, your solution work with the sample I provided.

However, when testing in the original dataset, it was not working.

the solution was to change

REMOVEFILTERS(Forecast[Series],Forecast[Material])

with

REMOVEFILTERS(Forecast[Material]),ALLSELECTED(Forecast))

By doing so, the measure keeps the selected filters expect for the Material, which is what I wanted.

thanks!