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 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'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!

7 Replies

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    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
      Icon for Resolver III rankResolver III
      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