Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Average Employee Count

Hi All! First time PBI user. I have a report that contains Employee Names, Unique ID, Hire Date, Termination Date and Status (Active, Terminated). My goal is to calculate monthly, quarterly and yearly average headcount. Internally, we calculate monthly headcount by taking the number of active employees on the 1st of the month, 15th of the month and last day of the month and dividing by 3. Then, the quarter and yearly are the sum of the monthly averages divided by the number of completed months. How do I go about calculating this in Power BI so that I can use a month/qtr/year slicer on my visuals? I don't know where to begin.
Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Ashish, thanks for the reply. Here are some screenshots of what I do in Excel and I am trying to translate it to Power BI. There are the exact files I am connecting to.

       

      Screenshot of what I use to calculate average employees

       

       

       

       

       

       

      Screenshot of my Terminations List

       

       

       

       

       

       

      And finally, the summary page of my results as they appear in Excel (Would use a combo of tables and visuals in Power BI)

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

         

        I cannot understand anything from those screenshots.  I will need to see that Excel file.