Forum Discussion

ukeasyproj's avatar
ukeasyproj
Icon for Helper II rankHelper II
8 years ago

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

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      ukeasyproj
      Icon for Helper II rankHelper II
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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