Forum Discussion

MagikJukas's avatar
MagikJukas
Icon for Resolver III rankResolver III
2 years ago
Solved

Sum up hierarchy not working (with sample)

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 ...
  • talespin's avatar
    talespin
    2 years ago

    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's avatar
    MagikJukas
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
MagikJukas
Icon for Resolver III rankResolver III
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      MagikJukas
      Icon for Resolver III rankResolver III
      2 years ago

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

       

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

      thanks!