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...
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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        v-ljerr-msft I saw that you didn't have the item "TotalHoursHund" in your original table. Which means "TotalHoursHund" got created within your new measure "OT". Didn't you get the error message saying that it can't find an item called "TotalHoursHund" when you type in the formula for "OT"?

         

        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 )
    )
)