Forum Discussion

Drobinson1's avatar
Drobinson1
Helper III
9 years ago
Solved

overtime calculation

 I am trying to calculate overtime by day and then have the column total up correctly.

 

Right now I have a measure that if the total hours worked for the day is greater than 8 then total the hours up and subtract by 8.

 

 

This works great fora  given day.  However the week total then is total hours worked-8.  In the example below i get 52.25.  When in reality I should get 20.25.

 

OT2 = IF(CALCULATE(SUM(RC_AoD_EmployeeSummsByFilekey[HoursHund]))>8,CALCULATE(SUM(RC_AoD_EmployeeSummsByFilekey[HoursHund]))-8,0)

 

Name        10/20/2016 10/21/2016 10/22/2016 10/23/2016 10/24/2016 Grand Total Total OT
John Doe          4                  4.25           3.75            4.25              4                  52.25       20.25

 

Show and Tell
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    The formula works fine for me without any error. Here is the sample pbix file for your reference:smileyhappy:

     

    Regards

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Drobinson1,

     

    According to your description above, you should use SUMX Function (DAX) in this scenario. The formula below is for your reference.

    OT =
CALCULATE (
    SUMX (
        RC_AoD_EmployeeSummsByFilekey,
        IF (
            RC_AoD_EmployeeSummsByFilekey[HoursHund] > 8,
            RC_AoD_EmployeeSummsByFilekey[HoursHund] - 8,
            0
        )
    )
)

     

    Regards

    • Drobinson1's avatar
      Drobinson1
      Helper III
      9 years ago

      That won't work either.   Becuase of the feed and varying labor laws in diffrent states the data is a bit odd and needs to be viewed both ways.  In states like CA it is broken seperately each day. This would be idea for everyone, but is not the way it really works.  In states like Ohio it is not overtime until 40 hours is reached for the workweek.  Then it gets put into overtime buckets. So what I am trying to do is total each employees hours by day and then if greater than 8 subtract 8 to get to the net amount and call that overtime.

       

       

       

      Employee         Date               Hourshund        Category        Location

      Jane Doe     11/9/2016                4                    Overtime          CA

      Jane Doe     11/9/2016               8                     Regular            CA

      John Doe     11/9/2016               9.5                     Regular         OH

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi Drobinson1,

         

        In this new scenario, we can use SUMMARIZE Function (DAX) to get a summary table first, then use SUMX Function (DAX) to get the right total value in Matrix. See my sample below.:smileyhappy:

         

        I assume you have a table called "RC_AoD_EmployeeSummsByFilekey" like below.

         

         

        Then you should be able to use the formula below to create the measure and show it in the Matrix with correct total value.

        OT = 
CALCULATE (
    SUMX (
        SUMMARIZE (
            RC_AoD_EmployeeSummsByFilekey,
            RC_AoD_EmployeeSummsByFilekey[Employee],
            RC_AoD_EmployeeSummsByFilekey[Date],
            RC_AoD_EmployeeSummsByFilekey[Location],
            "TotalHoursHund", SUM ( RC_AoD_EmployeeSummsByFilekey[Hourshund] )
        ),
        IF ( [TotalHoursHund] > 8, [TotalHoursHund] - 8, 0 )
    )
)

         

        Regards