Calculate Hours Worked Per Week For Each Employee ID

[Anonymous]

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

Hi @Anonymous 

Would a measure like this help?

Total by Emp and Week = 
    CALCULATE(
        SUM( 'Table'[Clocked Presence] ),
        ALLEXCEPT( 'Table', 'Table'[EmployeeID], 'Table'[Week] )
    )

Let me know if you have any questions.

Total by Emp and Week.pbix

[Anonymous]

Awesome that worked like a charm, I accepted it as the solution.