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])
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,
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
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,
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
We are excited to announce the Power BI Super Users!
Overview of Power BI 2020 release wave 2!
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.