cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

incorrect total for measure

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

 

 

 

5 REPLIES 5
Anonymous
Not applicable

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

@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
Not applicable

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

HoursWorked.PNG

 

Hope this helps,

Parker

 

@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
Not applicable

@ukeasyproj

 

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

 

Thanks,

Parker

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors