cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tryg2015
Frequent Visitor

Row division throughout matrix hierarchy

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())

 

Capture.PNG

 

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.

 

3 REPLIES 3
v-xicai
Community Support
Community Support

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.

 

Tryg2015
Frequent Visitor

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
Community Support
Community Support

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.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.