Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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.
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.
@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
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.
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 🙂
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
User | Count |
---|---|
84 | |
77 | |
69 | |
46 | |
41 |
User | Count |
---|---|
106 | |
50 | |
49 | |
40 | |
39 |