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, 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!

Need Help
  • 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.
  • 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.

14 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

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

    • barbforsman's avatar
      barbforsman
      Resolver I
      8 years ago
      Forgive my lack of knowledge, but I can’t see any options for attaching files. How do I do that?
      • parry2k's avatar
        parry2k
        Super User
        8 years ago

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

  • MarkS's avatar
    MarkS
    Resolver IV
    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
      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.

         

  • MarkS's avatar
    MarkS
    Resolver IV
    8 years ago

    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.

    • MarkS's avatar
      MarkS
      Resolver IV
      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.

      • MarkS's avatar
        MarkS
        Resolver IV
        8 years ago

        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.