Forum Discussion
Overtime Calculation
This question has been asked several times, but I wasn't able to make any of the solutions provided fit my scenario. I have a table with the following data:
Name, Date Worked, WeekEnding Date, Hours Worked, Work Order
I need to sum the Hours Worked by week by employee to determine overtime. I have used this formula for HoursPerWeek:
HoursPerWeek = calculate(
SUM(R550618NLK[Hours]),
filter(R550618NLK,R550618NLK[WeekEnding])
)
But finding Overtime is challenging me! I have tried:
OT = if([HoursPerWeek]>40,[HoursPerWeek] - 40,BLANK())
This works when I look at individual employees, but this doesn't sum up correctly in a matrix.
I've also tried using my Measure, HoursPerWeek:
Total OT =
CALCULATE (
SUMX (
MsrTable,
IF (
[HoursPerWeek] > 40,
[HoursPerWeek] - 40,
BLANK()
)
)
)
But this gives me blank.
What is the correct formula?
Help will be much appreciated!
Thank you! I have posted the file here.
Hi Anonymous,
Could you try the OT formula as:
OT = if([HoursPerWeek]>40,[HoursPerWeek] - 40*[EmployeeCount],BLANK())
and see if that works in your scenario.hi Anonymous,
This measure also seems to work
OTHours = CALCULATE ( SUMX ( SUMMARIZE ( R550618NLK, [WeekEnding], [Name], "Overtime", MAX ( [HoursPerWeek] - 40, 0 ) ), [Overtime] ), ALL ( R550618NLK[Pay Type] ) )however I would also check it, especially if an employee job title could change during the week.
14 Replies
- parry2k
Super User
if you can provide some sample data, will do the measure, just provide minimum data and mask any sensitive data.
- parry2k
it doesn't allow to attach data, you have to use google drive or dropbox or something like that to share the file.
MarkSResolver IV
Hi Anonymous,
Could you try the OT formula as:
OT = if([HoursPerWeek]>40,[HoursPerWeek] - 40*[EmployeeCount],BLANK())
and see if that works in your scenario.Actually, this works for subtotals by title, but the grandtotal in the matrix is still not correct.
Total Hours in the matrix are 8959.30 and OT total in the matrix is 8519.30, when OT should total 1119.30
I've updated the pbix file here.
Help will be greatly appreciated!!
- Ashish_Mathur
Hi,
Why should the answer be 1119.30? It should be 6,879.30. See the second table in the image below. For you to check, i have collpased the column labels at the monthly level. The summation of individual OT hours for each month add up correctly in last column. Here's the file.
- MarkS
Hi Anonymous
Possibly try this Calculated Column:
OT Hours = DIVIDE ( MAX ( CALCULATE ( SUM ( R550618NLK[Hours] ), ALLEXCEPT ( R550618NLK, R550618NLK[Name], R550618NLK[WeekEnding] ) ) - 40, 0 ), CALCULATE ( COUNT ( R550618NLK[Hours] ), ALLEXCEPT ( R550618NLK, R550618NLK[Name], R550618NLK[WeekEnding] ) ) )and see if this will work for you.
- MarkS
hi Anonymous,
This measure also seems to work
OTHours = CALCULATE ( SUMX ( SUMMARIZE ( R550618NLK, [WeekEnding], [Name], "Overtime", MAX ( [HoursPerWeek] - 40, 0 ) ), [Overtime] ), ALL ( R550618NLK[Pay Type] ) )however I would also check it, especially if an employee job title could change during the week.
- MarkS
Hi Anonymous,
I think that you were using the Calculated column formula as a measure to get that result. That formula should be entered as a calculated column on the R55.. table.
