incorrect total for measure

[ukeasyproj]

I have a time entry table that is set up as follows:

UserID   |  Entry Date |  Duration| User Exemption Status | PTO/Holiday

Duration is just how many hours the user logs that day, User Exemption Status is either Exempt or Non exempt (a user can only ever be one of these).  If PTO/Holiday column has a value, then that entry should be ignored in my calculations below.

my visual is just a simple table that has the following columns, UserID, Regular Hours, OT Exempt Hours, OT Non Exempt Hours

-regular hours is how much hours the user logs for the selected period (if it is above 40, then display 40 only)

-OT Exempt Hours is for Exempt Employees, how many hours did they log above 40 if any for the selected period

-OT Non Exempt Hours is for Non Exempt Employees, how many hours did they log above 40, if any for the selected period

I have a date range slicer that basically is set to 7 days at a time

These are the measures I created which give the correct values on a per user basis, but the grand total is not what I am expecting:

Regular Hours = IF( [Hours Worked] > 40, 40, [Hours Worked]) + 0

Hours Worked = SUMX(  FILTER(RELATEDTABLE('TT_TimeEntries (2)'), ISBLANK([PTO/Holiday]) = TRUE()    ), [Duration]) 

OT Exempt Hours = IF( [OT Exempt Hours Worked] > 40 , [OT Exempt Hours Worked] - 40, BLANK())

OT Exempt Hours Worked = SUMX( FILTER('TT_TimeEntries (2)', [Exemption Status] = "EXEMPT"), [Hours Worked])

OT Non Exempt Hours = IF( [OT Non Exempt Hours Worked] > 40 , [OT Non Exempt Hours Worked] - 40, BLANK())

OT Non Exempt Hours Worked = SUMX( FILTER('TT_TimeEntries (2)', [Exemption Status] = "NON EXEMPT"), [Hours Worked])

[Anonymous]

Hey @ukeasyproj

This is a common problem because most users expect the Total row to just be the sum of the detail rows. Turns out that Power BI will evaluate the total row the exact same as the detail rows unless you specify differently. You can achieve this by using the HASONEFILTER function, because the row data will contain a filter and the grand total will not. An example measure would look something like this:

RealMonthlySum = 
IF(
    HASONEFILTER(Table1[Date]),
    TOTALMTD(SUM(Table1[SalesAmount]), Table1[Date]), --Logic for Detail Rows
    SUM(Table1[SalesAmount]) --Logic for Grand Total row
)

If you want further explanation, you can watch a video tutorial I posted here.

Hope this helps,

Parker

[ukeasyproj]

@Anonymous

Hey thanks for that, I can defintely use that

but my regular hours measure introduces another level of complexity where the hours cannot exceed 40, if they do, it takes it it as 40

for example if user 1 logged 56 hours for the selected 7 day period, it should display 40 instead, and when the grand total is calculated (it should use 40 not 56)

I am unsure of how to implement that logic for the grand total part

[Anonymous]

@ukeasyproj

Gotcha, that condition makes it a little trickier. Try this:

 measure = 
IF(
   HASONEFILTER(Table1[UserId]),
   IF(
       [Hours Worked] > 40,
       40,
       [Hours Worked]
   ),
   SUMX(
       Table1,
       IF(
          [Hours Worked] > 40,
          40,
          [Hours Worked]
       )
   )
)

Because of the extra step, you need to use SUMX to specify how you want to take that sum for the Grand Total. Then you will get something like this:

Hope this helps,

Parker

[ukeasyproj]

@Anonymous

Hey thanks for that measure, it works fine

but when I add in additional columns to the table related to the user's meta data (such as user's supervisor name), the measure breaks

[Anonymous]

@ukeasyproj

Is that field from a different table? Could you post a snippet of the data that you're working with?

Thanks,

Parker