Forum Discussion

barbforsman's avatar
barbforsman
Resolver I
8 years ago
Solved

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, Hour...
Need Help
  • barbforsman's avatar
    barbforsman
    8 years ago

    Thank you!  I have posted the file here. 

  • MarkS's avatar
    MarkS
    8 years ago

    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.
  • barbforsman's avatar
    barbforsman
    8 years ago

    Thank you!!  That works great!!

  • MarkS's avatar
    MarkS
    8 years ago

    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.

barbforsman's avatar
barbforsman
Resolver I
8 years ago

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's avatar
Ashish_Mathur
Super User
8 years ago

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's avatar
    barbforsman
    Resolver I
    8 years ago

    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.