Row division throughout matrix hierarchy

[Anonymous]

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

•  Simple division (measure)

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.

[v-xicai]

Hi @Anonymous ,

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.

[Anonymous]

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.

[v-xicai]

Hi  @Anonymous ,

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.