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
ms007
Regular Visitor

Calculating over time hours for employees

Hi 

I need some help calculatin over time hours in a week if the total hours for an employee go over 40 hours in a week. The issue is that employees log hours by project, and can work on more than one project in the same week. My Power BI table may have two rows for one employee for a week if he worked on two projects. So here's an example:

 

Employee A, Project 1, Week of March 2, 2019, 15 hours

Employee A, Project 2, Week of March 2, 2019, 30 hours

 

The Over time hours correctly calculated should be OT = (30 + 15) - 40 = 5 hours for this Employee 1.

 

How can I achieve this?

1 ACCEPTED SOLUTION

Hi @ms007 ,

If you want to add up total OT hours for all employees in every week, we can use the following one:

OT =
CALCULATE (
    SUM ( Table1[hours] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Week] = MIN ( Table1[Week] )
    )
) - 40

Then only drag the measure and week to the visual.

Best Regards,

Teige

View solution in original post

4 REPLIES 4
TeigeGao
Solution Sage
Solution Sage

Hi @ms007 ,

Assume that we have a table like below:

PBIDesktop_qavdNf1tsV.png

We can create a measure like below:

OT =
CALCULATE (
    SUM ( Table1[hours] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Employee] = MIN ( Table1[Employee] )
            && Table1[Week] = MIN ( Table1[Week] )
    )
) - 40

The result will like below:

PBIDesktop_gy9Y4Sjc6h.png

Best Regards,

Teige

Hi, this calculation worked perfectly for what I needed, but did not resolve the whole problem. In my case employees can have different contracted hours. So instead of - 40 I would want to substract the individual contracted hrs (like 40 hrs in some cases and 34 ,25 or15 hrs etc in other cases ). That is I need to be able to substract a column (say 'contracted_hrs'). But how can I do this if 'contracted_hrs' is not summed, and I have a Calculate in the first part?

Hi Teige

Thnk you for your reply. This works. But I have one question. I want to add up the total OT hours for all employees. So how do I do that?

Hi @ms007 ,

If you want to add up total OT hours for all employees in every week, we can use the following one:

OT =
CALCULATE (
    SUM ( Table1[hours] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Week] = MIN ( Table1[Week] )
    )
) - 40

Then only drag the measure and week to the visual.

Best Regards,

Teige

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.