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,

 

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 )
    )
)
  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    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