Sum up hierarchy not working (with sample)

[MagikJukas]

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

[talespin · Accepted solution]

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

        )

[talespin · Accepted solution]

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

        )

[MagikJukas · Accepted solution]

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!

[Anonymous]

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.

[MagikJukas]

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

[Anonymous]

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

[MagikJukas]

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

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

thanks!

[MagikJukas]

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