From Data to Decisions: Revolutionizing HR Analytics with Power BI

5 min readFeb 25, 2023

Analyzing employee's presence using Power BI

Problem statement
Solution
Data
Task
Data Cleaning
Finding Total Employees Attendance %, Sick Leave % and Work from home %
Find the trends for it over time
Analyzing the same trends in week days
Additional Tasks
Final Report
Insights
What I’ve Learned
References

Problem statement

AtliQ Hardware, with multiple branches across India, provides computer hardware and peripheral manufacturers to its clients. The HR department of a company is facing challenges in monitoring employee attendance and identifying patterns of absenteeism. They need to have a comprehensive view of the attendance records of all employees, including the number of leaves taken and reasons for absence.

Solution

As a data analyst, to solve the problem of analyzing employee attendance and presence for HR, developing BI dashboard can help in identifying trends, patterns, and issues that could impact the company’s productivity and profitability.

Tools Used: Excel, PowerBI

Data

We are provided with the Excel datasheet which contains the employees data for 3 months between April 2022-June 2022. Data set contains employee code, employees name and employees attendance data along with attendance keys Eg: SL → Sick Leave.

Task

Find Total Employees Attendance %, Sick Leave % and Work from home %
Find the trends for it over time
Analyzing the same trends in week days

Data Cleaning

Data set in our excel looks like this.

I’ve made the first row as headers
Then removed the first row
Removed the Attendance key sheet as it wont be helpful during visualization
And unpivoted the all the rows except employee code and name on June, May and April 2022
And created a function so that if I’m going to add data in future it won’t have any impact

Data after cleaning looks like this

Finding Total Employees Attendance %, Sick Leave % and Work from home %

Employees Attendance % is considered a Presence% in our report. In order to find the Presence%, we need to find total present days and total working days. Present days are the actual working days.

For finding these values, I’ve created a table and added measure to do these calculations.

Total Days = COUNT('Final Data'[Value])
----------------------------------------------------------------------------------
Total Non working days = CALCULATE(COUNT('Final Data'[Value]), 'Final Data'[Value] in {"WO","HO"})
----------------------------------------------------------------------------------
Total Working Days = [Total Days]-[Total Non working days]
----------------------------------------------------------------------------------
Present Days = 

VAR Presentdays= CALCULATE(COUNT('Final Data'[Value]), 'Final Data'[Value]="P")

RETURN 
Presentdays + [WFH count]
----------------------------------------------------------------------------------
Presence % = DIVIDE([Present Days],[Total Working Days],0)

Using Card, we have found the presence %

Similarly, I have found for Sick leave % and Work from home% by creating new measures

First, I’ve added two new columns to find the WFH count and SL count and calculated the measures

WFH % = DIVIDE([WFH count],[Present Days],0)
----------------------------------------------------------------------------
SL % = DIVIDE([SL count],[Total Working Days],0)

Find the trends for it over time

Inorder to find the trends over time for all the three above mentioned measures, I’ve used area chart to get the insights with x-axis being date and y-axis being the measures we found.

Using filters, I have removed Weekends for getting proper insights.