overtime calculation

[Drobinson1]

 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

[v-ljerr-msft]

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]

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]

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.

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]

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

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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

Regards