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

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
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.

22 Responses to “Master Excel 2007 Ribbon with this Free Learning Guide”

  1. Finnur says:

    Thank you, kind sir. Well done with the baby making.

  2. doug churchill says:

    I cannot get signed up for your newsletter. I tied both this email address and churchill2001@hotmail.com. never a response.

  3. doug churchill says:

    I cannot get signed up for your newsletter. I tied both this email address and churchill2001_at_hotmail_dot_com. never a response for either attempt.

    • Chandoo says:

      @Doug, it shows that your email address is pending verification. Can you check your inbox (and may be spam folder too) for an email from me? The subject will be "Activate Subscription to Get your Free Excel Tips E-book"

  4. ajay says:

    Very Useful Info..Keep it up..

  5. Chandoo says:

    @Ajay.. you are welcome 🙂

  6. [...] Excel 2010 UI looks considerably better and less stressful than 2007. The colors are dull and subtle. The icons don’t call for attention unless you want to do something. The menus / ribbons feel smoother and slicker. [Learn to use Excel Ribbon with this Free e-Book] [...]

  7. Mark says:

    I can't open this pdf. I get the error message:

    You do not have the required license to open this file.

    Please request a license from the creator of the file, and add it using the license manager and they try opening it again.

    What gives??

  8. Mark says:

    I downloaded the file again and it worked this time. Strange. (First file was 116 KB, second was 1644 KB... ???)

  9. Michael says:

    Hi Chandoo,
    thanks for sharing your Excel 2007 learning experience with us; unfortunately the link to the pdf of the free Excel 2007 learning guide seems broken: my Acrobate Readers flags: "Unkown file type or corrupte data".
    Have a nice day
    Michael

  10. anja says:

    well done this is great

  11. Fabian says:

    Can somebody just provide a link the classic TAB exportedUI files for MS Office 2003 for us to use in office 2007/2010?. searching online, everybody just wnats to make a buck online with silly Classic Tab installers which do nothing more than inport exportedUI files for you.

    Don't give me a ribbon how to guide, just give me free exportedUI files. I should not have to pay anyone for this, it is free XML, MS should have included this to begin with.

  12. rocky says:

    thanks

  13. kartik says:

    Dear.
    There are a set of debit values and a set ot credit values in a column. I want a vba code by whcich the debit value plus a single / multiple credit value is zero that needs to be marked .
    finally i will come to know out of the avaibale debits which cannot be used the with avilable credits either single or multiple values.
    If multiple matching sets are available let it take the 1st or the 2nd one its not an issue.
    Column A Ref
    -1000 A
    -5000 B
    -8000 C
    800 A
    100 A
    100 A
    2000 B
    3000 B
    13000
    15000

  14. ridwan says:

    hi...
    how to make this add-ins and display in ribbon... check this sample : http://www.cprsoft.com/GCDemo01.htm
    thank you sir...
     

  15. Aleem Qamar says:

    Please tell me format painter short cut key In excel ?
    Thanks In Advance

  16. mano says:

    thankfully.likeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

  17. Gift Miag Mentor says:

    I am very much happy for such a great opportunity given to excel learners to advance their skills for the betterment of the future. I am a great user of this site and feel proud to have come across this web site.
    I appreciate this, because I didn't do much works in my project management studies using gantt chart. As of now are have now learned some advancement.

Leave a Reply