Forum Discussion
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 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?
Hi sdas028,
You should use the following formula and check if it works fine.average=COUNTROWS(Table)/DISTINCTCOUNT(Table[Emplyeename])
Best Regards,
Angelia
8 Replies
- 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.
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 ?
