Forum Discussion
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 |
| Nov | 3588 | 3586.5 |
| Dec | 3595 | 3589.333 |
| Jan | 3601 | 3592.25 |
| Feb | 3581 | 3590 |
| Mar | 3605 | 3592.5 |
| Apr | 3613 | 3595.429 |
| May | 3616 | 3598 |
| Jun | 3672 | 3606.222 |
| Jul | 3723 | 3617.9 |
| Aug | 3749 | 3629.818 |
| Sep | 3777 | 3642.083 |
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
6 Replies
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 BIAnonymous
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
you are welcome
Hi,
Does the Financial Year run from October to September? Also, share some data to work with (in a format that can be pasted in an MS Excel file.) or share the download link of your PBI file.
