Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Can anyone please help me with my DAX measure? I'm getting an incorrect total with my DAX below:
final dollar =
VAR cond1 = FILTER(
'Staff Data',
'Staff Data'[REC_LEAVE_CURR_BALANCE_DAYS]>'Staff Data'[FTE _Excessive Leave_]*40
)
VAR comprate_oncost =
CALCULATE(
MAXX('Staff Data','Staff Data'[COMPRATE])+(MAXX('Staff Data','Staff Data'[COMPRATE])*[On cost %]),
cond1
)
VAR hourly_rate =
CALCULATE(
comprate_oncost/(MAXX('Staff Data','Staff Data'[STD_HRS _Custom SQL Query_]*52.17856)),
cond1
)
VAR schedHrsPerWeekDiv5 =
CALCULATE(
MAXX('Staff Data','Staff Data'[SCHEDULE_STD_HRS_PER_WEEK]/5),
cond1
)
VAR RecBalance_RecThresholdFTE =
CALCULATE(
(MAXX('Staff Data','Staff Data'[REC_LEAVE_CURR_BALANCE_DAYS])-(MAXX('Staff Data','Staff Data'[FTE _Excessive Leave_]*40)))*schedHrsPerWeekDiv5*hourly_rate,
cond1
)
RETURN
SUMX(cond1,RecBalance_RecThresholdFTE)
The total should be 2,312,373, but what I get is
NB. The row values are correct .
Solved! Go to Solution.
@ajohn_ben First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
Thanks so much for this info, @Greg_Deckler .
**bleep**! I'm trying to replicate a Tableau dashboard onto Power BI and this supposedly simple function: displaying the GT of the visible rows on a matrix table is proving to be tedious. I hope MS implements your idea ASAP.
Thanks again.
@ajohn_ben First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |