Forum Discussion
Solved
Average Head Count by Week
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 ...
Hi sdas028,
You should use the following formula and check if it works fine.average=COUNTROWS(Table)/DISTINCTCOUNT(Table[Emplyeename])
Best Regards,
Angelia
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 |
Is this what is required as output ?
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
- Is week 21 a complete or partial week?
Do you mean did they work a full week?
