Working with overtime calculation

[powerbiuser101]

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 hours per day for each user, see if each user has gone over 8 hours and mark those overages as overtime. 

Example of my raw data:

user day hours

apple	01.04.2018	4
apple	01.04.2018	3
apple	01.04.2018	4
banana	03.04.2018	10
banana	05.04.2018	3

However, the problem is either the row value is wrong or the grand total is wrong. Here's what I have so far:

Overtime with Summarize = 
SUMX(
    SUMMARIZE(
        Table1; Table1[hours]);
    IF ( Table1[hours] > 8; Table1[hours] - 8; 0))

Overtime simple = 
var overtime =
    SUM(Table1[hours]) - 8

return
    IF(overtime < 0; 0; overtime)

Any help appreciated!

[MarkS]

Hi @powerbiuser101,

This will not answer your question, but this if statement

"IF ( Table1[hours] > 8; Table1[hours] - 8; 0)"  could be replaced by

MAX (Table1[hours] - 8; 0)

[Greg_Deckler · Accepted solution]

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)

[powerbiuser101]

Thanks @Greg_Deckler! Works perfectly! 

And thanks @MarkS on the tip. 

[Greg_Deckler]

Try using GROUPBY instead of SUMMARIZE. What is your expected output?