Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
Do you mean did they work a full week?
Hi @sdas028,
You should use the following formula and check if it works fine.
average=COUNTROWS(Table)/DISTINCTCOUNT(Table[Emplyeename])
Best Regards,
Angelia
Brilliant!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |