Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 Mon...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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