Overtime Calculation

[barbforsman]

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!

[barbforsman · Accepted solution]

Thank you!  I have posted the file here. 

[MarkS · Accepted solution]

Hi @barbforsman,

Could you try the OT formula as:

OT = if([HoursPerWeek]>40,[HoursPerWeek] - 40*[EmployeeCount],BLANK())

and see if that works in your scenario.

[MarkS · Accepted solution]

and see if that works in your scenario.

[barbforsman · Accepted solution]

Thank you!!  That works great!!

[MarkS]

Hi @barbforsman

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.

[barbforsman]

Thank you - but I get 3.53 for every data point?

[MarkS]

Hi @barbforsman,

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.

[barbforsman]

Ah - yes!  Thank you.  Thank you for the other formula as well - they both work great. 

[MarkS · Accepted solution]

hi @barbforsman,

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 · Accepted solution]

Hi @barbforsman,

Could you try the OT formula as:

OT = if([HoursPerWeek]>40,[HoursPerWeek] - 40*[EmployeeCount],BLANK())

and see if that works in your scenario.

[MarkS · Accepted solution]

and see if that works in your scenario.

[barbforsman]

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.

[barbforsman]

If I look at the first line in the 2nd table, there are only 3 field engineers.  Their OT actually totals 43.25 for January, not 454.75.

There is only 1 field tech.  His OT for January totals 8.5, not 149.25.   This is an excel table with expected hours, hours worked and OT hours for all employees. 

[barbforsman · Accepted solution]

Thank you!!  That works great!!

[parry2k]

if you can provide some sample data, will do the measure, just provide minimum data and mask any sensitive data.

[barbforsman]

Forgive my lack of knowledge, but I can’t see any options for attaching files. How do I do that?

[parry2k]

it doesn't allow to attach data, you have to use google drive or dropbox or something like that to share the file.

[barbforsman · Accepted solution]

Thank you!  I have posted the file here.