cancel
Showing results for
Did you mean:
Frequent Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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 4
Member

A given calendar week can have days that fall in different months.

You data doesn't have a date column nor a month column. So there's no way to calculate the average for a month

Frequent Visitor

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 ID

 INC01232 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.

Community Support Team

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

Frequent Visitor