fbpx
Search
Close this search box.

Employee Turnover Dashboard – Power BI for HR

Share

Facebook
Twitter
LinkedIn

Employee Turnover / Attrition Dashboard – Power BI

Jack – The recruiting hamster

Meet Jack. He is a recruiter at East Coasters Inc. In the first quarter of 2019, so far 17 people in Engineering, 12 people in R&D, 9 people in Customer Care and 7 people in Finance have left East Coasters. Jack could only manage to replace 12 of them. What should he do?

Buy Panadol, lots of it.

Jokes aside, people in HR know very well that the recruitment hamster wheel must go on. But you know what makes the HR manager’s life a little better? If you know employee turonver looks, you can manage it better.

So on that note, let’s see how you can create an interactive, fun and useful Employee Turonver dashboard using Power BI.

Quick demo of the HR Turnover dashboard

Before learning how to create this, just take a look at this beauty.

Start with data

Typical staff recruitment and turnover data looks like this:

  • Employee details (name, designation etc.)
  • Where they work (department, branch etc.)
  • Date of join
  • Date of leaving
  • Reason for leaving

Let’s assume this is how our data looks like. We have two sets of it. One for recruitment and another for leaving.

Download sample data

Load data and transform thru Power Query

Now that we have our data, let’s load it in to Power BI workbook. Open Power BI, click on Get data and point to your employee data set (in this case, the data came from an Excel file, for you this can be a SQL query, Oracle database or angry data dump from a bored data analyst in IT)

While at Power Query, it is a good idea to split the data in to dimension and fact tables. The exact set of tables depend on your input data. In our case, I have created below tables.

  • Fact Tables
    • Recruitments data – called staff
    • Leavers data – called leavers
  • Dimension Tables
    • Branches – dBranch
    • Departments – dDept
    • Designations – dTitle
    • Gender – dGender
    • Calendar (generated thru Power Query List.Numbers function) – calendar

The process of creating these tables is fairly straight forward. If you are not sure how to make them from your source tables, watch the video at the end of this article.

Load data and Model it in Power BI

At the end of this process, load data to Power BI and link up tables. Here is my data model. Dimension tables are in the middle.

Data model - Employee turnover dashboard - Power BI

Create some measures

Now that our data model is ready, let’s dax. I meant Data Analysis eXpressions, you silly. You can measure and analyze recruitment and leaver data in any number of ways. Since Power BI allows us to interactively explore and visualize data, I find that even simple measures can deliver powerful results (as you will see in the dashboard).

Here are a few measures you can create:
(Refer to data model diagram above if you are not sure what a field refers to)

Leaver Count = COUNTROWS(leavers)
Joinee Count = COUNTROWS(staff)
Tunrover % = DIVIDE([Leaver Count], [Joinee Count], blank())

Total Staff to date = 
CALCULATE(
	[Joinee Count]-[Leaver Count],
	FILTER(
		ALLSELECTED('calendar'[Date]),
		ISONORAFTER('calendar'[Date], MAX('calendar'[Date]), DESC)
	)
)

While the above 4 measures are simple, the next one is a bit tricky. So if you dax with two left hands, then ignore the next one. You can still create powerful reporting.

The next measure tells us about top 2 branches and their contribution to overall turnover.

Top 2 branch leavers total = 
    var t2 = topn(2,dBranch,[Leaver Count],DESC)
    var t2_names = CONCATENATEX(t2, dBranch[Branch], ", ", [Leaver Count], DESC)
return
    "Top 2 branches ("& t2_names &") account for " & format(divide(SUMX(t2, [Leaver Count]), CALCULATE([Leaver Count], all(dBranch)),0),"0%") & " of leavers"

Let’s get graphic

So our data is ready, measures are clicking. Time to place them in some visuals to see whats going on with our turnover. There are many options when it comes visualizing this kind of data. Just play with Power BI and keep what you like.

Here are a few options.

New Joinees vs. Leavers over time

Simple line chart with a text box for title. Uses [Joinee Count] and [Leaver Count] measures with Calendar[date] on horizontal axis.

Leavers by branch and gender

This next one is stacked bar chart with gender, branch and [leaver count]. We can then overlay a card visual with [top 2 branch leavers total] measure to see more info about top 2 branches.

Or a few cards with statistics

You can add multi-row cards to display statistics. When mixed with visual filters on relative date, you can get same measures in different context. See below for some inspiration.

Relative date filtering for the cards.

See top 10 designations of leavers

You can never go wrong with a black dress or good old fashioned table. A simple table of turnover % by job title (designation) will always look flash. But what if you have 100s of jobs. Simple, apply Top N filter and you can look at things that matter most.

Complete Turnover Dashboard

click to enlarge

Employee turnover  / attrition dashboard

Download Power BI workbook

Click here to download the Power BI workbook.

Video tutorial – Employee Attrition Dashboard

If you are still not sure how everything works, check out this simple tutorial. Make sure you follow along in Power BI for best results. The video explains how to transform data in Power Query, how to generate custom calendar, how to create data model, measure development, visual selection and formatting. It is quite in-depth and yet not too long. Check it out below. Or watch it on my YouTube channel.

Are you HR + Power BI?

Do you work in HR and use Power BI? How do you measure and analyze turnover? Please share your thoughts and tips in the comments box. Even if you don’t work in HR, I am sure you find this example very useful for Power BI, Power Query and dashboard development.

More Power BI examples

If you have just started with Power BI and want to learn how to use the tech, check out below resources.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

17 Responses to “Employee Turnover Dashboard – Power BI for HR”

  1. Kaushik Joshi says:

    its a fantastic use case for HR analytics in PBI. I do this each quarter using good old Excel. Now that the data is slowly comimg out of our HR Erp, i need to shift my energies from manual collation of data to letting it come from ERP and focussing more on Daxing. Dax as all techno stuff is initially intimidating but o am coming for it.....
    Will be also great if you can weigh R or Python vs PBI. Chandoo, with sincere thanks as always! Kaushik Joshi

  2. sandeep says:

    Great post!

  3. Sarav says:

    Awesome... as usual...

  4. saroj says:

    Please provide the contact number please

  5. Frederick says:

    great Chandoo

  6. Krishna says:

    This is great, Chandoo! Could you throw some more light on how we can go about tracking Promotions and pay changes with the same base employee data!

  7. Angie says:

    Hi Chandoo,

    While I think this (and all of your work) is great, it is not calculating true employee turnover. In very basic terms, Employee Turnover is defined as the total number of leavers in a given period divided by the average headcount for the same period (multiplied by 100).

    Other versions include annualized turnover, which equates to the average number of leavers per month YTD divided by average headcount x100. Then there is the rolling 12 turnover rate which is the sum of the count of leavers over the previous 12 months divided by the average headcount over the same period x100.

    I've created a series of dashboards with these numbers, but I would love to see what you come up with... my guess is your solution will be much more sophisticated than mine.

    Thanks so much for sharing your talents with the world!
    Angie

    • Charles says:

      Agree with Angie. The report is great with the filters by various demographic elements, but the core calculation needs to be changed. You are presenting a "net hire" figure, which has value. But for turnover, for what ever period the filters use, you calculate average headcount and the sum of "leavers."

      E.G.:
      January starting 100 + 10 hires - 5 leavers; 5/110 = 4.5% turnover
      February staring 105 + 10 hires - 5 leavers; 5/115 = 4.3%
      March starting 110 + 10 hires - 5 leavers; 5/120 = 4.2%
      Q1 ((110+115+120)/3) = 115 avg; 15 leavers/115 avg active = 13.0% turnover

      So how do you do a dynamic calculation that averages the monthly active headcount and sums the leavers for any selected period (month, quarter, YTD, YE)?

      Some people may vary the calculation method for active vs. what I show, but this should be good for illustration.

      Thanks!

      • Arjun M says:

        Thanks for the great video, Dear Chandoo.

        This may be useful

        Employee Turnover Rate = Employees Left / ([employee count at the starting of the period + employee count at the end of the period]/2) x 100

  8. Krishna says:

    Hi Angie,

    I'd love to connect with you and learn more about the turnover dashboards you've built. Is there a good email to reach you on?

  9. Megan says:

    I am curious as to how you go the 8 department tabs/slicers looking items at the top? I have been attempting to figure out how to insert those, but to not avail.

    Thank you,
    Megan

    • Bharath says:

      Hi Megan,

      It is basically a slicer which has been formatted in the form of buttons. In slicer visual, go to "General" tab and make the orientation as "Horizontal". This changes to button format and then you can play around with font colors with font color, outline color in settings.

  10. Sonca says:

    Great post and useful. Thanks.

  11. isje says:

    Thank you.
    It took time for me to understand, but it's really good. This is what I've been looking for.

  12. Heather says:

    Hi Chandoo! I tried the Top 2 card in your HR video. I am coming across an issue and I am not knowledgeable enough to understand how to fix it. The card works great as long as I have a department with 2 or more terminations. However, if the user selects a department that has less than 2 terms, the card spits out every single department name in the multi business unit company. I thought maybe I could set the "Filters on this visual" > "Show items when the value:" to "is greater than". However, when I select the card and try to change the filter on the visual, the "Show items when the value:" is set to "contains" and does not allow me to change it. Would you know how to write the DAX so that it filters for any department that has a term count value ">=2"?
    Below is my variation of your code:
    Top 2 Departments of Terminations Total =
    var t2 = topn(2,dDepartment,[Termination Count],DESC)
    var t2_names = CONCATENATEX(t2, dDepartment[Department Name], ", ", [Termination Count], DESC)
    return
    "Top 2 departments ("& t2_names &") account for " & format(divide(SUMX(t2, [Termination Count]), CALCULATE([Termination Count], all(dDepartment)),0),"0%") & " of terminations"

  13. Sam says:

    Really enjoyed this however I have tried several different ways and am struggling with managing the relationships. Can only link each dimension to one fact table, not multiples. Have troubleshooted and tried several different methods but alas.

  14. agustin says:

    hi
    good day Sir
    i have a issue complicate for me and i would like if you can help me please supose we have a script that save a file every day from conectivity some machines like this
    hostname- ip- date -status
    --------------------------------
    pc-a 1.1.1.1 2021/06/28 up
    pc-b 1.2.3.1 2021/06/28 up
    pc-c 1.1.2.1 2021/06/28 down

    the carpet have files from 1 mounth i want to visualizate the top 5 machines have been down the last 5 days
    beside which machine has been down for more days this mounth
    thanks in advance

    regards
    Agus

Leave a Reply