Do you want to learn how you can make a wonderful visualization in Power BI? Then, this blog is the right path you should thread. In this series, we will be visualizing employee attrition using Power BI

If you prefer videos to written articles, below is the video, else you can follow the step-by-step written tutorial.

Instructor: Daniel Ayangbile (LinkedIn)

What is Power BI?

Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Your data may be an Excel spreadsheet, or a collection of cloud-based and on-premises hybrid data warehouses. Power BI lets you easily connect to your data sources, visualize and discover what's important, and share that with anyone or everyone you want.

PowerBI is a business intelligence tool that is used for data visualization of data from various data sources and turning them into interactive dashboards.

This tutorial is centered on meeting a need in the business world which is basically centered on visualizing employee attrition using matrix visual in PowerBI.

Business Need: Resagratia is an international FMCG company with production plants in 5 African countries. Sandra, the global HR business partner will be discussing plans to reduce the attrition rate of the company at the 2021 Annual Business Review. In order to back up her claims which state that the attrition rate is high during the onboarding process, she has requested the business intelligence team to help analyze and visualize the employee attrition. She is hoping to see the attrition patterns and if possible any trend.

Dataset:

The Resagratia employee dataset contains 6 attributes (columns) which are:

1. Email Address: This is the email address of the employees
2. Site: This is the location of work of the employees
3. Department: This is about the department each employee works in
4. Hired Date: This is the date each employee gains employment with Resagratia
5. Attrition Date: This is the date each employee leaves Resagratia
6. Status: This is the employment status of each employee.

Below shows the first 10 entries of the dataset:

You can access the dataset here

Steps to providing solutions to the business need:

Fetching the Resagratia employees’ data

• Load the PowerBI application on your system and click on Get Data
• A box appears with a different data sources option.
• Select the appropriate type of the file you are about to use
• Choose the file from where it is stored on the computer

Transforming the data:

• Open the data from its source
• Select Table2 from the options.
• The data loads after which you select the Transform button. Transformation through the Power Query Editor is used in the cleaning process of data.

• After having loaded the data on the Power query editor, edit the name of the data from Table2 to HR_data
• After checking through each of the columns, it is observed that the data is devoid of missing entries and other abnormalities that may warrant cleaning (a very rare case though). As a result of this, no action is carried out here.
• With this click on Close and Apply button which is situated on the top left-hand side.

Creating the cohort column

This column will represent the grouping of employees according to their hired dates.

• From the Hired date column, filter the column to include employees hired within the month of January.
• Click on the dropdown button of Hired Date column and select Date filters.
• A list will drop down where you click on Between....
• Select the first and last date of the month of January from the box that appears

• On the Home tab, go to the Calculations wizard
• Select New Column.
• A new column appears, after which you just rename it to Cohort.
• Create a new variable vCurrentEmployee that groups the employees according to the month irrespective of their dates

Creating the tenure column

This column will be used in calculating the tenure of the employee in the company so far.

• DATEDIFF is a function that calculates the difference between two different dates.
• The IF statement in the formula calculates the difference between the date an employee was hired and the last date (2022-02-03), for an employee who is still active, while it calculates the difference between the date an employee is hired and the date he/she left in case the employee has been attrited.

💡 In case you want to format your DAX formula, you can easily do this on https://www.daxformatter.com

Creating the Tenure_table

• On the Home tab, go to the Calculations wizard and select New Table.
• A new table appears, after which you just rename it to Tenure_Table.
• GENERATESERIES function allows three (3) arguments: Start value which is selected to be 0, End value which is selected to be 24 and incremental value which is selected to be 1.

Creating Hired measure

Hired measure calculates the total number of employees:

• This measure Hired calculates the total number of employees from inception of the company using the DISTINCTCOUNT function accepting its argument as Email Address since no employee can have two addresses nor can two employees have one addresses
• Hired is calculated and thus dragged to the field while Card is selected in the Visualizations pane.

Creating Attrition measure

This is a new measure to calculate the total number of attrited employees:

• This measure Attrition calculates the total number of employees that have left the company.
• The CALCULATE function is used to calculate the members whose Status signifies Terminated out of all the hired employees so far.

Creating Employee attrition measure

• This measure creates three (3) variables; CurrentMonthAfter, CurrentFirstOrderMonth and vleft.
• vleft filters HR_Data and shows where my end of the month attrition is equivalent to end of the month hired.

Creating Employee Attrition % Measure

This simply adds an additional DIVIDE function that divides the result vleft by Hired.

This image presents the Employee attrition % by cohorts and month of attrition.

Sandra, the HR Manager can tell a story about the rate at which employees leave the company. Below are a few things amongst many others that this visual explains:

• The majority of the employees that are hired tend to leave within the first 5-6 months they are hired
• June 2021 has a high record of employee attrition also
• The highest rate of attrition occurred in December 2020

We do hope that you found this episode of the Data digest series exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Ready to Take Action?

1. Dive into our online data bootcamp! Learn at your own pace with our expert-led virtual programs designed to fit into your schedule. Become a qualified data expert in just 4 months and unlock your potential and land your dream career.

2. Learn more about our Data BootCamp programs by reading the testimonials of our graduates. Click HERE to access the testimonials.

3. You can also sign up for 1:1 personal tutoring with an expert instructor or request other solutions that we provide, which include data research, tech skill training, data products, and application development. Click HERE to begin.

4. Get in touch with us for further assistance from our team OR via email at servus@resagratia.com or our Whatsapp Number via +2349042231545.