Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

apple01.04.20184
apple01.04.20183
apple01.04.20184
banana03.04.201810
banana05.04.20183

 

 

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

overtime.PNG

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!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
MarkS
Resolver IV
Resolver IV

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
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler! Works perfectly! 

 

And thanks @MarkS on the tip. 

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.