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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!