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.
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.
The picture above helps to illustrate what is happening.
The item boxed pink is divided by the item boxed in purple to get the Dynamic TF Ratio
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?
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.