How to sum one column and take fix values of others

[Basdo]

Hi,

I've got a table with the following structure:

Date	Worker Name	Hours	Contract Hours per Day	Workday
1.2.2022	Tim	2	8	1
1.2.2022	Tim	3	8	1
1.2.2022	Tim	4	8	1
1.2.2022	Adam	6	7	1
2.2.2022	Tim	8	8	1
2.2.2022	Adam	8	7	1
3.2.2022	Adam	4	7	0

I want to calculate overhours, i.e. hours worked above contract hours per day.

The column "Contract Hours per Day" has a fixed value for each worker, in this case Tim = 8, Adam = 7

The column "Workday" shows if the day in question is a workday (1) or not (0).

Both these columns have always the same entry for each Date, so they should NOT be summed up.

The workers can book several different entries per day, thats why the column "Hours" needs to be summed up.

The expected result of the calculation in this example is:

Date	Worker	Overhours
1.2.2022	Tim	1
1.2.2022	Adam	-1
2.2.2022	Tim	0
2.2.2022	Adam	1
3.2.2022	Tim	4

I tried the following DAX expression:

Overhours = [Hours] - AVERAGE([Contract Hours per Day]*AVERAGE([Workday])

but the Average function always seems to take into account ALL table entries and not only those from the worker and date in question.

What would be a good way to solve this?

[Basdo]

@amitchandak I've got another question. The solution you proposed works well for single table entries but the total calculation is still wrong. How to fix this?

[amitchandak]

@Basdo , Please share an example

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Basdo]

Great amitchandak. Thank you very much.

[amitchandak · Accepted solution]

@Basdo , try a measure like

sumx(ADDCOLUMNS(summarize(Table, Table[Worker Name], Table[date]), "_hr" , sum(Table[Hours]), "_C", max(Table[Contract Hours per Day])), [_C] -[_hr])