Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have the following measure to calculate overtime hours as the difference between actual hours worked [Hours] and 'regular' hours [OT Base2]:
OT Hoursv2 = IF(CALCULATE([Hours], all(refDatesDAY[Date])) - calculate([OT Base2], all(refDatesDAY[Date]))<0,0, CALCULATE([Hours], all(refDatesDAY[Date])) - calculate([OT Base2], all(refDatesDAY[Date])) )
The RefDatesDay table is daily, so the measure is setup to give monthly totals (intended, daily calculations are not accurate). The totals my month are calculating correctly, but the total for the year is taking the difference of the two figures rather than the sum of the months:
I've tried wrapping in a SUMX, but no luck.
What DAX should I use to get the total above to be the sum of the months (4.5+2.5+6+20=33) rather than 2.5?
Thanks
Hi @connork,
Have you checked this article? Dealing with Measure Totals
Besides, if possible, please post some dummy data in source table.
Regards,
Yuliana Gu
Ok - here's the data, the measures and then a videw of the matrix with related columns:
DATA
dataTime
refDatesDAY
refEmployee
MEASURES
OT Hoursv2 = IF(CALCULATE([Hours], all(refDatesDAY[Date])) - calculate([OT Base2], all(refDatesDAY[Date]))<0,0, CALCULATE([Hours], all(refDatesDAY[Date])) - calculate([OT Base2], all(refDatesDAY[Date])) )
OT Base2 = calculate(sum(refDatesDAY[OTBaseHours]), filter(refDatesDAY, refDatesDAY[Date]<=[MeasTerm] &&refDatesDAY[Date]>=[MeasHire]))
Hours = sum(dataTime[EMP_HOURS])
MeasHire = maX(refEmployee[Hire])
MeasTerm = max(refEmployee[Term])
REPORT VIEW
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |