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.
Recruitment Data Leaving Data
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.
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
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.
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
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.
17 Responses to “Employee Turnover Dashboard – Power BI for HR”
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
Great post!
Awesome... as usual...
Please provide the contact number please
great Chandoo
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!
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
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!
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
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?
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
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.
Great post and useful. Thanks.
Thank you.
It took time for me to understand, but it's really good. This is what I've been looking for.
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"
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.
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