Forum Discussion
Weekly average headcount
I'm in a conundrum. I want to see the average amount of employees available per month. I made calculated table that looks like this:
Weeknum | WeekDay | Number of employees |
| 2018 Week 39 | 2 Monday | 6 |
| 2018 Week 39 | 3 Tuesday | 10 |
| 2018 Week 39 | 4 Wednesday | 7 |
| 2018 Week 39 | 5 Thursday | 12 |
| 2018 Week 39 | 6 Friday | 9 |
| 2018 Week 40 | 2 Monday | 8 |
| 2018 Week 40 | 3 Tuesday | 9 |
| 2018 Week 40 | 4 Wednesday | 7 |
| 2018 Week 40 | 5 Thursday | 12 |
| 2018 Week 40 | 6 Friday | 7 |
| 2018 Week 41 | 2 Monday | 9 |
| 2018 Week 41 | 3 Tuesday | 12 |
| 2018 Week 41 | 4 Wednesday | 9 |
| 2018 Week 41 | 5 Thursday | 11 |
| 2018 Week 41 | 6 Friday | 9 |
| 2018 Week 42 | 2 Monday | 7 |
| 2018 Week 42 | 3 Tuesday | 8 |
| 2018 Week 42 | 4 Wednesday | 7 |
| 2018 Week 42 | 5 Thursday | 9 |
Number of Employees = CALCULATE(DISTINCTCOUNT(Incidents[Opened By]), FILTER ( Incidents, [Opened By] <> [Caller ID]))
to show the average per weekday, I remove the weeknumber but leave everything else as is. as a result I get the following:
| WeekDay | Number of Employees |
| 2 Monday | 11 |
| 3 Tuesday | 13 |
| 4 Wednesday | 11 |
| 5 Thursday | 14 |
| 6 Friday | 12 |
obviously this is not the average amount of employees available. However, I have no clue how to fix it so that it does show the right average number of employees per weekday.
Kindly help me on this matter. Thank you in advance!
Hi Anonymous
Create measures
average = var count1 = CALCULATE(DISTINCTCOUNT('date Table'[weeknum]),ALLSELECTED('date Table')) return DIVIDE([Number of Employees],count1)If you want the following result
WeekDay sum of Employees average of Employees 2 Monday 30 30/4 3 Tuesday 39 39/4 4 Wednesday 30 30/4 5 Thursday 44 44/4 6 Friday 25 25/3 Friday (only occurs three times in table above)
create measures as below
average2 = AVERAGEX(FILTER(SUMMARIZE('date Table','date Table'[Date],'date Table'[weeknum],[weekday]),[Measure]=1),[Number of Employees])Best Regards
Maggie
4 Replies
The data I put there comes from the graphs that I have. due to the GDPR laws, I dont want to share too much information on the company, but I'll mask it. see table below:
NumberOpenedOpened ByCaller IDINC01232 18/10/2018 9407156 9407156 INC01233 18/10/2018 9406682 9401567 INC01234 18/10/2018 9406720 9770478 INC01235 18/10/2018 9770639 9770639 INC01236 18/10/2018 9770327 9770327 INC01237 18/10/2018 9900435 9770289 INC01238 18/10/2018 9406322 9006419 INC01239 18/10/2018 9322356 9322356 INC01240 18/10/2018 9900435 9312888 INC01241 18/10/2018 9770478 9770478 INC01242 18/10/2018 9406322 9404822 INC01243 18/10/2018 9770095 9770095 INC01244 18/10/2018 9404861 9009531 INC01245 18/10/2018 9022088 9022088 This is linked with a date table.
The date table does include weeknumber, day, weekday, month, year etc.
Hi Anonymous
Create measures
average = var count1 = CALCULATE(DISTINCTCOUNT('date Table'[weeknum]),ALLSELECTED('date Table')) return DIVIDE([Number of Employees],count1)If you want the following result
WeekDay sum of Employees average of Employees 2 Monday 30 30/4 3 Tuesday 39 39/4 4 Wednesday 30 30/4 5 Thursday 44 44/4 6 Friday 25 25/3 Friday (only occurs three times in table above)
create measures as below
average2 = AVERAGEX(FILTER(SUMMARIZE('date Table','date Table'[Date],'date Table'[weeknum],[weekday]),[Measure]=1),[Number of Employees])Best Regards
Maggie