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
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 ?
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
