Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Summary-level calculation

Hello! Here is the request in a nutshell. Details will follow. I need to apply the measure calculations at the current group level or context of the matrix drilldown. I can do it in relation to the grand total, but I need it in relation to each level.
Details:
I'm trying to solve the following problem and am wondering if you could help. I have revenue, GP$, which is gross profit (revenue less cost), and the GP%, which is GP$ divided by revenue and have all of that for each item and for the grand total. What I'm trying to do is to calculate what would the GP% be if I were to remove the current item from the calculation. I actually managed to get that done for each item with respect to the grand total. I need to "fix" this such that the calculation is done with respect to one level above. So, for instance, SKU1 is in CSG1. SKU1's GP% is 90%. CSG1's GP% is 45.6%. If I were to exclude SKU1 from the calculation, the GP% should now be the total GP$ for SKU2+SKU6 (that is $1200+$2000) divided by the total revenue for both SKUs from the Revenue column: $2000+$6000=$8000. So, $3200/$8000=40%. Yet, the picture below shows 36.6% because it is calculating the impact of removing the current item on the entire total. I need it to calculate based on the total for the current group level--CSG1 total. I managed to do that for the DSM levels D1 and D2 because i used isinscope with DSM as the column parameter. However, I need that to work at every level: the SKU impact is in relation to CSG; CSG impact is in relation to DSM, and, finally, DSM impact is in relation to the grand total. Is that possible? Thanks so much in advance.

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Even if you think that this can't be achieved, I would appreciate if you let me know, so I don't keep spinning wheels. Thanks so much!

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

    According to your description, it seems that you need to create a new table(with ITEM column) for slicer:

    ForSlicer = VALUES('Table'[ITEM])

    Then create a measure:

    Measure = 
    var _gp= CALCULATE(SUM('Table'[GP $]),FILTER('Table','Table'[ITEM] in ALLSELECTED('ForSlicer'[ITEM])))
    var _re=CALCULATE(SUM('Table'[Revenue]),FILTER('Table','Table'[ITEM] in ALLSELECTED('ForSlicer'[ITEM])))
    return DIVIDE(_gp,_re)

    The final output is shown below:

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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks Anonymous . I'm away from work now. I will review and will get back to you by the end of the week

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi, Anonymous. Thanks so much for your effort and apologies for the dealy as I was away. Unfortunately, this doesn't give me what I need. You see, I need to calculate each hierarchy level separately. So, If my current immediate hierarchy total is 1000, and my own number is 100, then removing my 100 will leave us with a 900 for the total of my immediate hierarchy. Please, see the below example. Thanks so much.