Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Power BI Community,
I was hoping somebody could help. I`m looking for a measure that would show average employee headcount per year, including, part years. I extract a report on a monthly basis on 1st of each month and this is included in the table as "Report Date".
What I would like to show is what was the average for say 2019 which would be the count of employees "RESID" for each month divided by 12 - this would work well for each completed year. I would like to extend this measure to show the average count for the current year which is a part year - so if updated the data in March the 2020 year average would show as the count of "RESID" for each month in the year divided by 3, April would show as the count of "RESID" for each month in the year divided by 4, and so on.
Please see link to data below.
Many thanks in advance,
Brendan
Solved! Go to Solution.
@Bfaws
Use this measure for the correct average:
Yearly Avg Employees Count =
AVERAGEX(
SUMMARIZE(
ALLEXCEPT('Resource reports - year average',Rolling_Calendar_Lookup[Year]),
Rolling_Calendar_Lookup[Fiscal Year],
Rolling_Calendar_Lookup[Month Name]
),
[Employee_count]
)
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS 👍 to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Bfaws , did not see the pbix yet. see if this one can help
@Bfaws
Use this measure for the correct average:
Yearly Avg Employees Count =
AVERAGEX(
SUMMARIZE(
ALLEXCEPT('Resource reports - year average',Rolling_Calendar_Lookup[Year]),
Rolling_Calendar_Lookup[Fiscal Year],
Rolling_Calendar_Lookup[Month Name]
),
[Employee_count]
)
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS 👍 to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Great 👍
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS ? to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |