Showing results for 
Search instead for 
Did you mean: 
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())




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.


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,



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


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.

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



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


Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!