Average Head Count by Week

[sdas028]

Hi, I am trying to calculate average head count by ISOWEEK. I have a data table that contains employee number, employee name, date of shift, position, isoweek. As the data has an inidvidual line for each employee on each day, if I do a count there is for example 20 heads based on 4 people working 5 days a week, and DISTINCTCOUNT gives 4 heads, but that is not the average of the heads. I've tried using an average and distinctcount combination but that doesnt seem to work either. Any suggestions?

[Seward12533]

Wim need more information to help. At least Picture of data model and some representative data if not a link to copy of a sample workbook.

[sdas028]

Hi

Here is a sample lot of the data I have, there is employee name and position as well but didnt want to include that information

Employee Number	Hours Worked	Date	Isoweek
50172	5	30/04/2018	18
50437	3.75	30/04/2018	18
50711	3.75	30/04/2018	18
50055	10.25	1/05/2018	18
50437	11.25	1/05/2018	18
50711	11.25	1/05/2018	18
51124	11.5	1/05/2018	18
50055	11	2/05/2018	18
50172	12.75	2/05/2018	18
50437	11.5	2/05/2018	18
50711	11.75	2/05/2018	18
51124	11.75	2/05/2018	18
50055	7.5	3/05/2018	18
50172	13.25	3/05/2018	18
50437	13.25	3/05/2018	18
50711	11.25	3/05/2018	18
51124	14	3/05/2018	18
50055	9	4/05/2018	18
50172	11	4/05/2018	18
50437	9.75	4/05/2018	18
51124	10.25	4/05/2018	18
50055	10	5/05/2018	18
50172	11.75	5/05/2018	18
50711	10.75	5/05/2018	18
51124	6.75	5/05/2018	18
50055	8.5	6/05/2018	18
50172	11.25	6/05/2018	18
50437	10.5	6/05/2018	18
50711	10.5	6/05/2018	18
50172	12	7/05/2018	19
50437	10.25	7/05/2018	19
50711	12.5	7/05/2018	19
50055	8.75	8/05/2018	19
50437	8	8/05/2018	19
50711	9	8/05/2018	19
51124	7	8/05/2018	19
50055	11.5	9/05/2018	19
50172	12.5	9/05/2018	19
50437	11.25	9/05/2018	19
50711	13	9/05/2018	19
51124	11	9/05/2018	19
50055	7.5	10/05/2018	19
50172	10.25	10/05/2018	19
50437	9.5	10/05/2018	19
50711	11	10/05/2018	19
50055	7.5	11/05/2018	19
50172	10.25	11/05/2018	19
50437	9.75	11/05/2018	19
51124	9	11/05/2018	19
50055	8.75	12/05/2018	19
50172	9	12/05/2018	19
50711	5.25	12/05/2018	19
51124	4	12/05/2018	19
50437	10	14/05/2018	20
50172	7.75	15/05/2018	20
50437	8.25	15/05/2018	20
50711	8.75	15/05/2018	20
50172	9.75	16/05/2018	20
50437	10	16/05/2018	20
50711	9.75	16/05/2018	20
50055	7.5	17/05/2018	20
50172	8.5	17/05/2018	20
50437	8.5	17/05/2018	20
50711	9	17/05/2018	20
50055	7.5	18/05/2018	20
50437	10.25	18/05/2018	20
50055	9.25	19/05/2018	20
50172	9.5	19/05/2018	20
50711	10	19/05/2018	20
50055	7	20/05/2018	20
50172	9	20/05/2018	20
50437	8.75	20/05/2018	20
50711	9.25	20/05/2018	20
50172	10.75	21/05/2018	21
50711	10.75	21/05/2018	21
50055	7.5	22/05/2018	21
50437	12	22/05/2018	21
50711	10.75	22/05/2018	21
50055	1.75	23/05/2018	21
50172	3	23/05/2018	21
50437	2	23/05/2018	21
50711	2.25	23/05/2018	21
50172	8.5	24/05/2018	21
50437	7.75	24/05/2018	21
50711	7.5	24/05/2018	21
50437	6.5	25/05/2018	21
50055	9.5	26/05/2018	21
50172	10.5	26/05/2018	21
50711	9.75	26/05/2018	21
50055	13.25	27/05/2018	21
50172	13.25	27/05/2018	21
50437	12	27/05/2018	21
50711	6.5	27/05/2018	21

[erikajain02]

Is this what is required as output ?

[sdas028]

I have managed to also get to this table - however I would have thought that the average for week 21 should be 5? 20 shifts in one week worked by 4 people should give an average of 5, or perhaps I am incorrect in my thinking and possibly overthinking the calculation

[Seward12533]

Is week 21 a complete or partial week?

[sdas028]

Do you mean did they work a full week?

[v-huizhn-msft · Accepted solution]

Hi @sdas028,

You should use the following formula and check if it works fine.

average=COUNTROWS(Table)/DISTINCTCOUNT(Table[Emplyeename])

Best Regards,
Angelia

[sdas028]

Brilliant!