Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

WeekDayNumber of employees
2018 Week 392 Monday6
2018 Week 393 Tuesday10
2018 Week 394 Wednesday7
2018 Week 395 Thursday12
2018 Week 396 Friday9
2018 Week 402 Monday8
2018 Week 403 Tuesday9
2018 Week 404 Wednesday7
2018 Week 405 Thursday12
2018 Week 406 Friday7
2018 Week 412 Monday9
2018 Week 413 Tuesday12
2018 Week 414 Wednesday9
2018 Week 415 Thursday11
2018 Week 416 Friday9
2018 Week 422 Monday7
2018 Week 423 Tuesday8
2018 Week 424 Wednesday7
2018 Week 425 Thursday9

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:

WeekDayNumber of Employees
2 Monday11
3 Tuesday13
4 Wednesday11
5 Thursday14
6 Friday12

 

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
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

Create measures

average = var count1 = CALCULATE(DISTINCTCOUNT('date Table'[weeknum]),ALLSELECTED('date Table')) return DIVIDE([Number of Employees],count1)

3.png

 

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])

4.png

 

Best Regards

Maggie

View solution in original post

4 REPLIES 4
keajht31
Frequent Visitor

Try AVERAGEX( VALUES([Weekday], [Number of headcount) )

 

 

v-juanli-msft
Community Support
Community Support

Hi @Anonymous

Create measures

average = var count1 = CALCULATE(DISTINCTCOUNT('date Table'[weeknum]),ALLSELECTED('date Table')) return DIVIDE([Number of Employees],count1)

3.png

 

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])

4.png

 

Best Regards

Maggie

Anonymous
Not applicable

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

Anonymous
Not applicable

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

INC0123218/10/201894071569407156
INC0123318/10/201894066829401567
INC0123418/10/201894067209770478
INC0123518/10/201897706399770639
INC0123618/10/201897703279770327
INC0123718/10/201899004359770289
INC0123818/10/201894063229006419
INC0123918/10/201893223569322356
INC0124018/10/201899004359312888
INC0124118/10/201897704789770478
INC0124218/10/201894063229404822
INC0124318/10/201897700959770095
INC0124418/10/201894048619009531
INC0124518/10/201890220889022088

This is linked with a date table.
The date table does include weeknumber, day, weekday, month, year etc.

Occupation per Day.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.