Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I have in my dataset categorized the rows into 4 different levels/hierarchy. As you can see, the matrix automatically categorizes and aggregates the numerical value to the parent level throughout the hierarchy correctly.
My issue is as such, I am interest in analyzing my cost levels relatively to my revenue. Simple division does not really help as the formula does not apply to all levels
DIVIDE(-SUM('Finance'[Numerical]);CALCULATE(SUM(Finance[Numerical]);Finance[Level 1]="Revenue");BLANK())
I am fully aware that my formula is making reference to a “level 1” row hence I get a blank on all other levels. I have tried following measure to filter through the hiearchy without help:
IF(ISFILTERED('Finance'[Level 4]);DIVIDE(-SUM('Finance'[Numerical]);CALCULATE(SUM('Finance'[Numerical]);ALL('Finance'[Level 4]));BLANK());IF(ISFILTERED('Finance'[Level 3]);DIVIDE(-SUM('Finance'[Numerical]);CALCULATE(SUM('Finance'[Numerical]);ALL('Finance'[Level 3]));BLANK());IF(ISFILTERED('Finance'[Level 2]);DIVIDE(-SUM('Finance'[Numerical]);CALCULATE(SUM('Finance'[Numerical]);ALL('Finance'[Level 2]));BLANK());IF(ISFILTERED('Finance'[Level 1]);DIVIDE(-SUM('Finance'[Numerical]);CALCULATE(SUM('Finance'[Numerical]);ALL('Finance'[Level 1]));BLANK());BLANK()))))
But this measure on divides the cost levels to the nearest parent level, whilst I am only interested in diving all levels with the revenue.
Is it possible to create a measure that divides all the rows to the revenue and bypass the “nearest parent” and yet aggregate correctly to the parent level?
Thank you.
Hi @Tryg2015 ,
You may change your measure like DAX below.
Simple division=DIVIDE(-CALCULATE(SUM('Finance'[Numerical])),CALCULATE(SUM(Finance[Numerical]),Finance[Level 1]="Revenue"),BLANK())
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Amy,
Thanks for your reply.
Your proposal was my first take on this issue. On top of my text you will see that I have posted the exact same measurement.
Hi @Tryg2015 ,
Compared with your original formula, I added the CALCULATE function in the formula, maybe you can have a try.
Simple division=DIVIDE(-CALCULATE(SUM('Finance'[Numerical])),CALCULATE(SUM(Finance[Numerical]),Finance[Level 1]="Revenue"),BLANK())
Best regards
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |