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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
connork
Helper I
Helper I

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
Anonymous
Not applicable

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

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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