cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
connork Regular Visitor
Regular Visitor

Calculated Measure Totals

DAX is not exact here (actual data much messier) but example as follows:

Goal: get subtotal of overtime hours by employee AND dept as of as of 5/2/17 (assume DATA1 ends here)

 

 

 

 

Data sets: 

Data1: actual hours by employee by day

REF1: working days list by date

REF2: employee standard working hours per day

 Calculated Measures

FTE%  = IFERROR( REF2[STD_HRS]/8,0)

REGHRS = REF1[WORKDAY] * 8 * [FTE%]

OT HRS = IF(  CALCULATE(SUM(DATA1[HOURS]) - [REGHRS]>0,   CALCULATE(SUM(DATA1[HOURS])-[REGHRS],  0)

Result: Subtotals/grand totals for OT hrs are calculating based on subtotals, need sum of above. 

 

Any help on how to change the OT Hrs measure to correctly get subtotals is appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
scottsen Senior Member
Senior Member

Re: Calculated Measure Totals

The standard technique for "my subtotals are wrong" is "wrap it in a sumx".

 

Total OT=SUMX(Employees, [OT Hrs])

 

Since [OT Hrs] is correct on a per employee basis, you can just walk all employeeds adding up their OT Hrs...

1 REPLY 1
scottsen Senior Member
Senior Member

Re: Calculated Measure Totals

The standard technique for "my subtotals are wrong" is "wrap it in a sumx".

 

Total OT=SUMX(Employees, [OT Hrs])

 

Since [OT Hrs] is correct on a per employee basis, you can just walk all employeeds adding up their OT Hrs...