Forum Discussion

powerbiuser101's avatar
powerbiuser101
Advocate I
8 years ago
Solved

Working with overtime calculation

Hi,   Tried searching the various other threads on this topic but they didn't really work for my case.   Users can have multiple entries per day depending on activity. I want to sum the total hou...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    OK, I think I got it:

     

    Measure 3 = 
VAR overtime = SUM('#Overtime'[hours]) - 8
VAR tmpTable = SUMMARIZE('#Overtime',[user],[day],"Hours",SUM('#Overtime'[hours]))
VAR tmpTable1 = ADDCOLUMNS(tmpTable,"Overtime",IF([Hours]-8<0,0,[Hours]-8))
VAR overtimeTotal = SUMX(tmpTable1,[Overtime])
RETURN IF(HASONEFILTER('#Overtime'[user]),IF(overtime<0,0,overtime),overtimeTotal)
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

OK, I think I got it:

 

Measure 3 = 
VAR overtime = SUM('#Overtime'[hours]) - 8
VAR tmpTable = SUMMARIZE('#Overtime',[user],[day],"Hours",SUM('#Overtime'[hours]))
VAR tmpTable1 = ADDCOLUMNS(tmpTable,"Overtime",IF([Hours]-8<0,0,[Hours]-8))
VAR overtimeTotal = SUMX(tmpTable1,[Overtime])
RETURN IF(HASONEFILTER('#Overtime'[user]),IF(overtime<0,0,overtime),overtimeTotal)