cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Matrix Subtotals Different calculation....

I know there are a variety of posts on this but I couldn't seem to find one that had the solution that I was looking for... I'm hoping one of your wonderful minds can help me out. 

 

Here is a little context. I do work for a couple of Oil and Gas companies and am trying to build out a dynamic netback tool (Netback is essentially the same as calculating the Cost of goods). So there are a variety of costs that go into getting a barrel of oil out of the ground, some fixed and some allocated. As I am in the development stage I am treating everything as a fixed cost and can later break it out. A majority of the costs are not allocated to a well but to the entire field. With the current pandemic and oil prices, this company is trying to determine what wells are economic and what are not. The purpose of this tool is to run scenarios where you shut-in (turn off) a well, thus no more oil (which means cashflow) is coming from that asset, but since there are a large number of fixed costs they need to get reallocated to the other wells. I have managed to get this tool to what feels like 90% of the way but now I'm stuck. 

 

I have a disconnected table with each well listed out which is used as a slicer. When a well is selected it sets the production to 0 and recalculates the total oil. From that, I get a ratio that I can multiply my costs by so that you can get costs down to a well level. Annotation#1.png
Annotation#2.png

 

 

 

 

 

 

 

 

 

 

As you can see you can select a well and it will set the production to 0 and the ratios (%) will change accordingly. Now what I need to do is get everything to sum up correctly.

Annotation#3.png

 

 

The picture above helps to illustrate what is happening.

  1. The item boxed pink is divided by the item boxed in purple to get the Dynamic TF Ratio
  2. The Static Hierarchy amount (highlighted yellow) is multiplied by the ratio to get the green highlighted item
    Everything is working correctly so far now this is were I am struggling...

I need the blue box to sum the red box instead of following the same operation as what I listed.

Does anyone have any suggestions on how to accomplish this? 

 

Thank you in advance!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Community Support
Community Support

HI @TheCAKurtle,

So your matrix is designed based on UWI fields and multiple measure formulas?

AFAIK, measure formula with specific filters and conditions can not work properly on the visual total level with aggregated features.

In my opinion, I'd like to suggest you take a look at Greg_Deckler's 'measure total calculation' blogs to know more about this scenario, do modification to add if statement checks the current row contents level and write a total level expression to your measures to manually handle the total level calculation.

If you still confused about coding formula, please share some dummy data to test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin SHeng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors