Forum Discussion

TheCAKurtle's avatar
TheCAKurtle
Helper I
6 years ago
Solved

Dynamic Budgeting Tool

I am currently trying to build a dynamic budgeting tool for an energy company. With oil prices being so low currently this company would like to have the ability to "turn off" a well to help determin...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi TheCAKurtle ，

     

    1. Create a slicer table,put the categories in the column:

     

    2.Delete the relationship between table and the slicer table:

     

    3.Create a measure as below:

     

    Measure = IF(SELECTEDVALUE('slicer table'[category])=SELECTEDVALUE('Table'[Category]),0,'Table'[Ratio])

     

    Finally you will see:

     

    Is the above what you need?

     

    Here is my sample .pbix file you can refer to.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Yes.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

It is really tough to say without seeing the data but maybe something along the lines of:

Measure =
  VAR __Selected = 'Table'
  VAR __UnSelected = EXCEPT(ALL('Table'),__Selected)
RETURN
  SUMX(__UnSelected,[Column])

So basically, you grab everybody in context within the slicer (__Selected). You then get everyone (ALL) and then filter out __Selected using EXCEPT. Then you just SUMX across __UnSelected for some Column. And you have effectively wiped out any impact from the "selected" wells, in effect, setting their values to zero.

 

TheCAKurtle's avatar
TheCAKurtle
Helper I
6 years ago

Greg_Deckler 

 

Thanks for the quick reply. I am using this calculation to feed into several others so from this I get a ratio for how much production a particular well contributes which is then used to allocate costs. When a wells production goes to 0 I would like those ratios to be recalculated so that costs can be reallocated. I don't think this solution will work for that... Or am I mistaken?