Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
thomazinh
Helper I
Helper I

CALCULATE and ALL - Needing help keeping denominator constant

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. 

thomazinh_0-1645645385265.png

 

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. 

thomazinh_1-1645645609119.png

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

1.png

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.

 

View solution in original post

4 REPLIES 4
thomazinh
Helper I
Helper I

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. 

 

thomazinh_3-1645717997219.png

 

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. 

 

thomazinh_4-1645718015334.png

 

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. 

 

 

@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

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

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.

1.png

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
Super User
Super User

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%. 

TheoC_0-1645687326022.png

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

TheoC_1-1645687390075.png

Hope this helps mate!

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.