HR Analytics Orian Lab – Power BI Case Study

In this Power BI case study, we will be exploring a dataset for a fictitious software company called Orian Labs.

We will carry out exploratory data analysis and will use DAX to help build powerful visualizations. We will finish our analysis by diving deeper into attrition and what factors impact attrition. This analysis will help the organization determine what action they will need to take to retain more employees.

We’ll finalize the case study by making design changes to our report that provides a clean, branded design.

Skip to the → Final Part of The Report

1st Part of HR Analytics Orian Lab – Exploratory Analysis

In the first part of the Power BI report we will do some exploratory analysis to check the data by creating new measures and columns.

We will check the following in particular:

KPIs
Employee Hiring Trends
Active Employees By Department
And Active Employees By Department & Job Role

##Measures and Columns created for Exploratory Analysis and 1st Part of report.

TotalEmployees = COUNT(DimEmployee[EmployeeID])

InactiveEmployees = IF(DimEmployee[Attrition]="Yes", 0, 1)

SumofInactiveEmployees = SUM(DimEmployee[InactiveEmployees])

ActiveEmployeesCount = COUNT(DimEmployee[ActiveEmployees]) -[SumofInactiveEmployees]

%AttritionRate = [InactiveEmployees]/[TotalEmployees]*100

2nd Part of HR Analytics Orian Lab

In the 2nd part of the Power BI report we will do some further analysis on its employee demographics and performance.

We will check the following in particular:

Demographics
Employees Marital Status
Employees by Ethnicity & Average Salary
Performance Tracking
Rating Tracking

##Measures and Columns created for Further Analysis
--Youngest Employee
MinAge = MIN(DimEmployee[Age])

--Oldest Employee
MaxAge = MAX(DimEmployee[Age])

-- For Age Bins
AgeBins = 
SWITCH(
    TRUE(),
    'DimEmployee'[Age] <= 20, "20",
    'DimEmployee'[Age] >= 21 && 'DimEmployee'[Age] <= 29, "20-29",
    'DimEmployee'[Age] >= 30 && 'DimEmployee'[Age] <= 39, "30-39",
    'DimEmployee'[Age] >= 40 && 'DimEmployee'[Age] <= 49, "40-49",
    'DimEmployee'[Age] = 50, "50"
)

-- Environment Satisfaction
EnvoirnmentSatisfaction = CALCULATE( 
MAX(
FactPerformanceRating[EnvironmentSatisfaction] ), 
USERELATIONSHIP(
FactPerformanceRating[EnvironmentSatisfaction], DimSatisfiedLevel[SatisfactionID]))

--
JobSatisfaction = MAX(FactPerformanceRating[JobSatisfaction])

--
WorkLifeBalance = CALCULATE(
    MAX (
        FactPerformanceRating[WorkLifeBalance] ), 
USERELATIONSHIP (
FactPerformanceRating[WorkLifeBalance], 
        DimSatisfiedLevel[SatisfactionID]))

--
RelationshipSatisfaction = CALCULATE( 
    MAX(
        FactPerformanceRating[RelationshipSatisfaction]), USERELATIONSHIP(FactPerformanceRating[RelationshipSatisfaction], DimSatisfiedLevel[SatisfactionID]))


--
SelfRating = CALCULATE(
    MAX(FactPerformanceRating[SelfRating]), 
    USERELATIONSHIP(FactPerformanceRating[SelfRating], DimRatingLevel[RatingID]))

--
ManagerRating = CALCULATE(
    MAX(FactPerformanceRating[ManagerRating]), 
    USERELATIONSHIP(FactPerformanceRating[ManagerRating], DimRatingLevel[RatingID]))

--
EmployeeFullName = DimEmployee[FirstName]&" "& DimEmployee[LastName]

--
LastReviewDate = IF(MAX(FactPerformanceRating[ReviewDate])= BLANK(),"No review yet", MAX(FactPerformanceRating[ReviewDate]))

--
NextReviewDate = 
VAR ReviewOrHire = IF(
    MAX( DimEmployee[HireDate]) = BLANK(),
    MAX( DimEmployee[HireDate]),
    MAX(FactPerformanceRating[ReviewDate])
    )
    RETURN
    ReviewOrHire + 365

Final Part of The Report

In this final part of the report, we’ll be focusing on delivering insights on attrition and what factors affect employee retention. Finally, we’ll be cleaning up the overall layout of the report to create a user-friendly, clean, and branded experience.

##For the final part of the report below Measures were Created

TotalEmployeesDate = CALCULATE( [TotalEmployees], USERELATIONSHIP(DimEmployee[HireDate], DimDate[Date]))

--
InactiveEmployeesDate = CALCULATE( [InactiveEmployees], USERELATIONSHIP(DimEmployee[HireDate], DimDate[Date] ))
--
%AttritionDateRate= [InactiveEmployeesDate]/[TotalEmployeesDate]