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 Exemp...
Need Help
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

 

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

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

      ukeasyproj

       

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

       

      Thanks,

      Parker