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.
19 Responses to “How to Distribute Players Between Teams – Evenly”
An excellent solution, especially for large data sets.
Another solution without using solver would be to assign the player with the highest score to Team 1, the 2nd to team 2, 3rd to team 3, 4th to team 3, 5th to team 2, 6th to team 1, 7th to team 1 and it continues. This method would end up with a Std Dev of 0.001247219. This works best with a distribution with lower Std Dev for the dataset.
Full Disclosure: this is not my idea, remember reading something a few years ago. Think it may have been Ozgrid
thinking back I now remember why I read about it. About 10 years back I had to distribute around 300 team members into 25-30 odd teams. Used this method based on their performance scores. I used the method I described to do this and the distribution was pretty fair.
Solver would have saved me a ton of time though 🙂
I think the issue with you first Solver approach was that you took the absolute value of the sum of team deviations (which should always be zero except for rounding) instead of the sum of the absolute values (which is a reasonable measure of how unbalanced the teams are).
Here's another simple algorithm you could use: you start from the top (with players sorted from high to low), and at each step allocate the next player to whichever team has the smallest total so far. You can implement it dynamically with some formulas so it will update automatically when the data changes.
If the scores were more widely distributed (so that this might end up with not all teams the same size), you could add a constraint to only pick among the teams which currently have fewest players at each step, or just stop adding to any team when it hits its quota.
When I tried it on the sample, I got the three teams below, with a STDEV of 0.000942809 (i.e. about half of what Solver got to).
Team 1: John, Hugo, Tom, Josh, Eric, Zane, Charles, Andrew
Team 2: Barry, Michael, Kenny, Joe, Xavier, Patrick, Oliver, William
Team 3: Henry, Steven, Ben, Frank, Kyle, Edward, Cameron, Lachlan
Thanks for sharing!
Hi,
I was looking at all the solutions and this is closest to what I intended to do. I am dividing a bunch of players into 3 soccer teams. Players availability is also a factor while deciding the teams.
So the steps the excel needs to do is as follows:
1) In availability column if "yes" go to next
2) Equally divide 'Goalkeepers', 'Strikers', 'Defenders' basis their quality
So the end result gives each 3 teams a balance of players playing at different positions.
Can this be done on Google spreadsheet with only availability as an input from the user and rest calculates by itself.
Sorry for asking such a pointed question, but I have been struggling to find a solution for it for sometime now!
Hi Ishaan,
I am working on a similar problem at the moment, so I am wondering if you ever found a solution and if you are willing to share what you did.
Hi everyone, this is a variation of the famous Knapsack Problem https://en.wikipedia.org/wiki/Knapsack_problem.
I had to use a VBA implementation recently as part of a problem, where we ar trying to allocate teams of an organization into different locations (we are a large company with many different team). The goal was to optimally allocate teams to individual buildings without putting too many teams into one building and not splitting teams apart.
As we had around 400 teams of different sizes, solver couldn't handle it anymore. Luckily there is a Knapsack algorithm implementation in VBA readily available on the internet :).
I also went with a heuristic approach first!
An interesting mathematical solution but what if Eric and Xavier can't stand each other or Patrick is best friends with Steven - the real life problems that effect "even" teams.
@Joe
You can add more criteria like
If Eric and Xavier can't stand each other
=OR(AND(E15=1,E16=1),AND(F15=1,F16=1),AND(G15=1,G16=1))
It must be False
If Patrick is best friends with Steven
=OR(AND(E5=1,E17=1),AND(F5=1,F17=1),AND(G5=1,G17=1))
It must be True
Note that the 2 formulas above are exactly the same
except for the ranges
One must be True = Friends
One must be False = Not Friends
Nice Post!
Just one question What if number of players are not even or equally divisible.
Nice post Hui!
I download your workbook and just try to change in options the Precision Restriction from 10E-6 to 10-8 and the Convergence from 10E-4 to 10E-10. The process take almost the same time, but the results was great.
The standard deviation I got was 0,000471.
Team 1: John, Tom, Kenny, Frank, Eric, Xavier, Edward, Zane
Team 2: Steven, Hugo, Ben, Joe, Josh, Oliver, Cameron, William
Team 3: Barry, Henry, Michael, Kyle, Patrick, Charles, Andrew, Lachlan
Great application of Solver! Thanks for the link!
Great explanation. Well done... However, I tried with 6 teams of 4 players and solver never did finish.
How about vba code for the same data set.
I have 3 column A B C wherein A has text and B has number Wherein C is blank. And in C1 been the header C2 where I want the name to come evenly distributed the number which is in Column B.
My Lastcolumn is 1000.
Sorry if I'm being slow here, but how is 'Team Score' calculated? I've gone through the explanation several times but it seems to just appear.
@Hrmft
This process uses the Solver Excel addin
Solver is effectively taking the model and trying different solutions until it gets a solution that meets all the criteria
Then solver puts the solution into the cell and moves to the next cell
So yes it appears to "just appear"
Hi ! Thank you so much ! Works great 🙂
I cannot get the fourth Equation to work in my excel spreadsheet
You have =($E$2:$G$25=0)+($E$2:$G$25=1)=1 as a SUMIF solution, I have, =($F$2:$H$13=0)+($F$2:$H$13=1)=1 as my solution but it does not work. The only thing I changed is the ranges. Any suggestions?
Thank you.
Jim
I cannot get the fourth Equation of TURE or FALSE statements to work in my excel spreadsheet You have =($E$2:$G$25=0)+($E$2:$G$25=1)=1 as a SUMIF solution, I have, =($F$2:$H$13=0)+($F$2:$H$13=1)=1 as my solution but it does not work. The only thing I changed is the ranges. Any suggestions?
Sorry I left some of it out in the previous question,
Thank you. Jim