CALCULATE and ALL - Needing help keeping denominator constant

[thomazinh]

I have two tables where I'm trying to keep the denominator the same across all tables to see a % Associated Breakout of a category. I want to return a DAX measure, Assoc Act Labor that calculates the: 

Cost for that Discipline row / Total Labor Discipline Cost

Discipline Breakout and Labor Discipline are from the same field in my dataset. Cost is also the same field in the dataset, and all items are in the same table. I'm working in a certified dataset and do not have permisions to create another table or edit the queries.

My data is split into two different tables below so that I could check my DAX calculating the total Cost for Labor Discipline. Below are my tables and values. 

The Total Labor Discipline Cost is $1.3M, and I am needing this value to stay as a constant denominator. For example, I am expecting the Assoc Act Labor for the Consumables Discipline Breakout to be $23,215/$1.3M = 1.75%. 

My DAX returning the Total Cost for Labor Breakout matches the table total and is below. 

I tried to run a KEEPFILTERS but was unsuccessful. Any help is appreciated. 

[thomazinh]

Hi @TheoC ,

I recreated your code in a new PBI file, and I am still struggling to get it to work. Here is the FILE LINK. 

SumFixed is outputting an error when I RETURN for _2. To fix this, I can RETURN _1, but then loose the ability to filter once I apply the slicer and select B or another value. Additionally, _2 is not used at all in this case. 

Above I am RETURNING _1 in the SumFixed Measure. When I select a ID, the filters remove the SumFixed value. Is it possible to have the Division column show the values? For instance, I'd expect the B row to show 0.26 in the Division Column and E to show 0.16. Below I return _2 producing an error. 

Please let me know what steps I can do to remedy this. I appricate your help thus far and replying to my post. Thank you. 

[TheoC]

@thomazinh apologies. can you adjust VAR _1

VAR _1 = CALCULATE ( [SumAmount] , FILTER ( ALL ( 'WbsCode Forecast' ) , _Filter ) ) 

 From there, after RESULT, change it to _1 Instead of _2.

Apologies, on phone!

Thanks heaps,

Theo

[Anonymous · Accepted solution]

Hi @thomazinh ,

Try this code to create [SumFixed] measure.

SumFixed = 
VAR _Filter = FILTER(ALL( Table1), Table1[ID] IN {"A" , "C" , "D"})
VAR _1 = CALCULATE([SumAmount],_Filter)
RETURN
_1

Result is as below.

Best Regards,
Rico Zhou

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

[TheoC]

Hi @thomazinh 

You can achieve the above with a couple of measures:

SumAmount = SUM ( 'Table'[Amount] )

SumFixed =

VAR _Filter = FILTER ( 'WbsCode Forecast' , 'WbsCode Forecast'[WbsCode.Discipline.name] IN { "SMI" , "PLI" , "PFI" , "MTI" } )
VAR _1 = CALCULATE ( [SumAmount] , FILTER ( ALL ( 'WbsCode Forecast' ) , _Filter ) ) 

RETURN

_1

Division = DIVIDE ( [SumAmount] , [SumFixed] , 0 )

Output is as below.  You can see that 47 / 31 = 1.52 or 152%. 

Similarly, filtering on B for example gives 0.26 or 26%:

Hope this helps mate!

Theo 🙂