Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Average Headcount Calculation

I need to calculate average headcount    Will need help in arrving at below number in power bi. My excel look this   Month Closing Headcount Avergae HC - Average function Oct 3585 3585 ...
Need Help
Show and Tell
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    Anonymous 

    1. you need to have a date column or year month column

     

    2. you can create a column

    Column = AVERAGEX(FILTER('Table','Table'[year month]<=EARLIER('Table'[year month])),'Table'[Closing Headcount])

     

    or a measure

    Measure = AVERAGEX(FILTER(all('Table'),'Table'[year month]<=max('Table'[year month])),'Table'[Closing Headcount])

    pls see the attachment below

Shaurya's avatar
Shaurya
Memorable Member
3 years ago

Hi Anonymous,

 

Use the following formula:

 

Average Headcount = SUMX('Table',IF('Table'[Month]<=EARLIER('Table'[Month]),'Table'[Closing],0)) / SUMX('Table',IF('Table'[Month]<=EARLIER('Table'[Month]),1,0))

 

Mark this post as a solution if that works for you!
Consider taking a look at my blog: How to Export Telemetry Data from Azure IoT Central into Power BI