Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
Try AVERAGEX( VALUES([Weekday], [Number of headcount) )
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
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
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |