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.

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.

















13 Responses to “Using pivot tables to find out non performing customers”
To avoid the helper column and the macro, I would transpose the data into the format shown above (Name, Year, Sales). Now I can show more than one year, I can summarize - I can do many more things with it. ASAP Utilities (http://www.asap-utilities.com) has a new experimental feature that can easily transpose the table into the correct format. Much easier in my opinion.
David
Of course with alternative data structure, we can easily setup a slicer based solution so that everything works like clockwork with even less work.
David, I was just about to post the same!
In Contextures site, I remember there's a post on how to do that. Clearly, the way data is layed out on the very beginning is critical to get the best results, and even you may thinkg the original layout is the best way, it is clearly not. And that kind of mistakes are the ones I love ! because it teaches and trains you to avoid them, and how to think on the data structure the next time.
Eventually, you get to that place when you "see" the structure on the moment the client tells you the request, and then, you realized you had an ephiphany, that glorious moment when data is no longer a mistery to you!!!
Rgds,
Chandoo,
If the goal is to see the list of customers who have not business from yearX, I would change the helper column formula to :
=IF(selYear="all",sum(C4:M4),sum(offset(C4:M4,,selyear-2002,1,columns(C4:M4)-selyear+2002)))This formula will sum the sales from Selected Year to 2012.
JMarc
If you are already using a helper column and the combox box runs a macro after it changes, why not just adjust the macro and filter the source data?
Regards
I gotta say, it seems like you are giving 10 answers to 10 questions when your client REALLY wants to know is: "What is the last year "this" customer row had a non-zero Sales QTY?... You're missing the forest for the trees...
Change the helper column to:
=IFERROR(INDEX(tblSales[[#Headers],[Customer name]:[Sales 2012]],0,MATCH(9.99999999999999E+307,tblSales[[#This Row],[Customer name]:[Sales 2012]],1)),"NO SALES")
And yes, since I'm matching off of them for value, I would change the headers to straight "2002" instead of "Sales 2002" but you sort the table on the helper column and then and there you can answer all of your questions.
Hi thanks for this. Just can't figure out how you get the combo box to control the pivot table. Can you please advise?
Cheers
@Kevin.. You are welcome. To insert a combo box, go to Developer ribbon > Insert > form controls > combo box.
For more on various form controls and how to use them, please read this: http://chandoo.org/wp/2011/03/30/form-controls/
Thanks Chandoo. But I know how to insert a combobox, I was more referring to how does in control the year in the pivot table? Or is this obvious? I note that if I select the Selected Year from the PivotTable Field List it says "the field has no itens" whereas this would normally allow you to change the year??
Thanks again
worked it out thanks...
when =data!Q2 changes it changes the value in column N:N and then when you do a refreshall the pivottable vlaues get updated
Still not sure why PivotTable Field List says “the field has no itens"?? I created my own pivot table and could not repeat that.
Hi, I put the sales data in range(F5:P19) and added a column D with the title 'Last sales in year'. After that, in column D for each customer, the simple formula
=2000+MATCH(1000000,E5:P5)
will provide the last year in which that particular customer had any sales, which can than easily be managed by autofilter.
Somewhat longer but perhaps a bit more solid (with the column titles in row 4):
=RIGHT(INDEX($F$4:$P$19,1,MATCH(1000000,F5:P5)),4)
[…] Finding non-performing customers using Pivot Tables […]