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
Bfaws
Helper III
Helper III

Average employee headcount per year including average of part year

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.

 

Click here for .pbix file 

 

Many thanks in advance,

 

Brendan 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy

 

Many thanks - that has done the trick.

 

Much appreciated.

 

Brendan

@Bfaws 

 

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.