Summary of case study
This case study is to apply skills developed through the Power BI course offered by Data Camp using a real-world problem. This is the second project assignment.
The Problem
In this case study, a fictitious client, Atlas Labs, have the primary goal of monitoring Key HR metrics on employees and understanding what factors impact attrition (Employee attrition is the naturally occurring, voluntary departure of employees from a company)
Project Approach
As we build their report, it is key to understand the process we will follow in our report development in PowerBI. The process is summarized below:
Step 1: Building the data model and analyzing the data
Requirements gathering
Data Connections
Data Transformation
Building the data model
Writing DAX measures
Step 2: Report Design
Branding
Defining the report layout
Building your report with chart visualizations
The Dataset: Requirements gathering, Data Connections and Data Transformation
For this case study, we will use the Kimball modelling approach as we model our data.
The Fact table stores the information about employee yearly reviews. We will also be working with multiple dimension tables to provide more context to the data we are working with. The final data model will follow a snowflake schema below.
The first step we will take is to open an empty PowerBI file and load and prepare our dataset. We will import all the CSV files and load them into PowerBI. Ensure that the data is clean. You may refer to this article on the expectations of clean data. Ensure you add either Fact OR Dim at the beginning of each table name, depending on the type of table it is.
Building the data model: Date dimension and relating tables
In any Power BI report, it is recommended to have a dedicated date table for accurate date and time reporting. In this stage, we will also model our data. This will enable us to connect our six different tables.
The first step we will take is to create a dedicated date table. Here is the DAX code we will use for the calculated Date Table. Next we will connect the following tables:
Connect the DimDate table to FactPerfomanceRating and the DimEmployee table. Note that the last relationship will be inactive because we cannot have more than one active relationship between the same tables at once, in PowerBI.
Connect the DimEducationLevel table to the DimEmployee table
Connect FactPerfomanceRating table columns (EnvironmentSatisfaction, JobSatisfaction, RelationshipSatisfaction and WorkLifeBalance) to DimSatisfactionLevel table and use EnvironmentSatisfaction as the active connection.
Connect FactPerfomanceRating table columns (SelfRating, ManagerRating) to DimRatingLevel and use the SelfRating column as the active connection.
Exploring the data
We will explore our data and produce high level metrics to understand the attrition at the company. Rename page 1 of your PowerBI file to “Overview” and create a new table called _Measures to store all the measures that we will create. We will then display these measures using the card visual in our overview page. Let’s create the measures below:
A measure to take count of all the employees
TotalEmployees = DISTINCTCOUNT(DimEmployee[EmployeeID])
Measures that take count of all employees that are currently active or inactive
ActiveEmployee = CALCULATE([TotalEmployees], DimEmployee[Attrition] = "No")
InactiveEmployee = CALCULATE([TotalEmployees], DimEmployee[Attrition] = "Yes")
Attrition Rate based on the measures we have created above.
% Attrition Rate = [InactiveEmployee]/[TotalEmployees]
Hiring trends over time
Next, we will analyze Atlas Labs' hiring trends over time to see where they have had the biggest growth in employees.
We will create a new measure called TotalEmployeesDate that uses the CALCULATE() function on our TotalEmployees Measure and the USERELATIONSHIP function in the filter. You may read more about USERELATIONSHIP function here
TotalEmployeesDate =
CALCULATE (
[TotalEmployees],
USERELATIONSHIP ( DimEmployee[HireDate], DimDate[Date] )
)
Go ahead and create a stacked column chart. Add attrition to the legend on the chart to see the split of employees by active vs inactive. Change the X-axis from continuous to Categorical and Rename the chart to “Employee Hiring Trends”
Analyzing departments and Job Roles
The next step, we will look into the typical roles department managers are hiring into the organization. This will enable every department to plan for new hiring requests in the future.
Create a clustered bar chart to show ActiveEmployees by Department. Rename this chart to “Active Employees by Department”. Add a treemap visualization to show Active employees by department and job role. Rename this chart to “Active Employees by Department and job role.”
Keys Insights so far
Atlas Labs has employed over 1,470 people
Atlas Labs currently employs over 1,200 people
The largest department so far is Technology
The attrition rate for employees leaving the organization is 16%
Is Atlas labs competitive enough as an employer?
Demographics: age and gender
The next step is to develop a good understanding of the diversity and inclusion metrics. We will focus on employee age and gender. Let’s create a new page in the report called “Demographics”
Below we can see the minimum and maximum value for age displayed by card visuals.
We want to take a closer look at the age distribution of employees so we will create a conditional column called AgeBins that separates employees' ages by bins in the following structure: <\20, 20-29, 30-39, 40-49, 50>.
=Table.AddColumn(#"Changed Type", "AgeBins", each if [Age] >= 50 then "50>" else if [Age] >= 40 then "40-49" else if [Age] >= 30 then "30-39" else if [Age] >= 20 then "20-29" else "<20")
Next, we will visualize our results. We will create a visualization that shows Employees by age and another visualization that shows Total employees value distribution across Age Bins and Gender.
It would be beneficial to add a page level filter that enables you to look at the report page based on whether an employee is currently active or not.
Marital Status and ethnicity
Next, we will be looking at further employee information regarding marital status and ethnicity. We will first visualize the count of all employees by marital status
Next, we will create a measure, AverageSalary inside the measure table. This works out the average salary of all employees.
AverageSalary = AVERAGE(DimEmployee[Salary])
Below is a visualization of employees by their average salary and ethnicity.
Performance Tracker 1:
The HR Team would like to track the performance of the employees based on yearly performance reviews. We will create a new page in the report called “Performance Tracker”.
We will add a slicer with employee full name which has single select and search enabled. We will go on to add a card which shows selected employee start date, last review date and next review date
Performance Tracker 2
Next we would like to take a closer look at individual review ratings. We will create satisfaction metrics inside the _Measures table and visualise them.
EnvironmentSatisfaction = CALCULATE(
MAX(FACTPerformanceRating[EnvironmentSatisfaction]),
USERELATIONSHIP(FACTPerformanceRating[EnvironmentSatisfaction], DimSatisfiedLevel[SatisfactionID]))
JobSatisfaction = MAX(FactPerformanceRating[JobSatisfaction])
RelationshipSatisfaction = CALCULATE(
MAX(FACTPerformanceRating
[RelationshipSatisfaction]),
USERELATIONSHIP(FACTPerformanceRating
[RelationshipSatisfaction], DimSatisfiedLevel[SatisfactionID])
)
More Insights uncovered
Majority of employees are between 20-29 yrs
- Atlas Labs employs 2.7% more women than men
Employees who identify as
Non Binary makeup 8.5% of total employees
White have the highest average salary
Mixed or multiple ethnic groups have one of the lowest average salaries
From the charts we’ve built, we can also see that the employees that have been considered frequent travelers have the highest attrition rate despite only making up 19% of the total hires.
Creating a cohesive report
The final step is preparing to deliver our report to the key stakeholders at Atlas Labs. We will focus on delivering insights on attrition and what factors affect employee retention.
Here is the Overview Dashboard.
Here is the final report delivered.
Thank you for reading
Author: Gcinithemba Sherilyn Maphosa
Data Wired