Calculate Hours Worked Per Week For Each Employee ID

Question

I am very new to Power BI and cannot figure out the DAX to solve my problem. I have a table of data in Power BI that looks like the following table:

Site	EmployeeID	Name	Date	Clocked Presence	Week
ABC	1234	KatyM	10/26/2023	10.5	1
ABC	1234	KatyM	10/27/2023	11.42	1
ABC	1234	KatyM	10/31/2023	9	1
ABC	5567	BruceK	11/1/2023	12	2
ABC	5567	BruceK	11/2/2023	10.4	2
ABC	9945	AndyL	10/26/2023	10.2	1
ABC	9945	AndyL	11/7/2023	11.8	2
ABC	9945	AndyL	11/9/2023	12.2	3

I need to create a DAX formula that will sum up how many hours each employee worked for each week. So for example, I need to know the total number of hours EmployeeID 1234 worked in week 1, week 2, week 3 and so on. Same for the other employees. Once I have the total number of hours each employee worked each week, I will then use a dax formula to calculate how many overtime hours they worked over 40 for each week. No matter what I try, I can only get the total number of hours worked for all employees for all weeks, but not the hours an employee worked for a specific week. There are other columns in my data, but the table posted above is showing just the info I need. This seems like an easy DAX formula, but no matter how much googling I have done each solution I have tried does not work. Please help! Thank you.

gmsamborn · Accepted Answer

Hi&nbsp;Anonymous&nbsp;&nbsp;Would a measure like this help?Total by Emp and Week = 
    CALCULATE(
        SUM( 'Table'[Clocked Presence] ),
        ALLEXCEPT( 'Table', 'Table'[EmployeeID], 'Table'[Week] )
    )&nbsp;Let me know if you have any questions.&nbsp;Total by Emp and Week.pbix&nbsp;

Forum Discussion

Calculate Hours Worked Per Week For Each Employee ID

2 Replies

Related content

Calculate Other Trained Employees

Counting Active employees per month

Missing employee

Calculate percentage of hours worked by each employees per day and per store

Employee FTE calculation