Forum Discussion
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
- AnonymousNot 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!
- AnonymousNot 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.- AnonymousNot applicable
Thanks Anonymous . I'm away from work now. I will review and will get back to you by the end of the week
- AnonymousNot 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.