Atliq Technologies: HR Management Report
Business Problem and Key Questions
Atliq Technologies are a large wholesale company with multiple offices nationwide. The manager at one of these offices wants to track employee presence in office to better plan team meetings on the days when presence is highest, and to flag employees who are taking to much sick leave. As the data analyst for the HR team, I've been tasked with creating a report that shows the monthly presence rate.
Atliq's HR team has 3 standard metrics in each report:
Employee presence percentage
Employee work from home percentage
Employee sick leave percentage
In addition to those standard metrics, he is also interested in the following key questions:
What day of the week are the most people present?
What day of the week are the most people working from home?
What day of the week are the most people on sick leave?
What is the trend of these 3 results over time?
What employees are taking the most sick leave?
Data Preparation
To prepare my data, I first had to connect the data to Power BI.
I downloaded the CSV file I needed:
Attendance_Sheet_apr-jun
After renaming the necessary columns and verifying data types, I clicked over to the first sheet and started visualizing the data.
Approach
For the 3 standard metrics, I used the following approach:
I created columns and measures first to get the data I needed:
Created a column to track the days employees were working from home
(work from home = 1, half work from home = 0.5, present = 0)
WFH Count = SWITCH(TRUE(),
'Data'[Value] = "WFH",1,
'Data'[Value] = "HWFH",0.5,
0)
Calculated the total of these days
WFH Count = SUM('Data'[WFH Count])
Used a variable to count how many days employees were in office, and added this to the days they were working from home. This gave the total present days
Present Days =
VAR presentdays = CALCULATE(COUNT('Data'[Value]), 'Data'[Value]="P")
RETURN
presentdays + [WFH Count]
Used variables to find the total working days and non working days, then subtracted them for the total working days
Total Working Days =
VAR totaldays = COUNT('Data'[Value])
VAR nonworkdays = CALCULATE(COUNT('Data'[Value]), 'Data'[Value] IN {"WO", "HO"})
RETURN
totaldays-nonworkdays
Created a column to track the days employees were on sick leave
( Sick leave = 1, half sick leave = 0.5)
SL Count = SWITCH(TRUE(),
'Data'[Value] = "SL",1,
'Data'[Value] = "HSL",0.5,
0)
Calculated the total of these days
SL Count = SUM('Data'[SL Count])
Then I used these results to get my metrics:
Divided the total present days by the total working days to get the Presence % :
Presence % = DIVIDE([Present Days], 'Measure Table'[Total Working Days],0)
Divided the total of working from home days by the present days to get the Work From Home % :
WFH % = DIVIDE([WFH Count],[Present Days],0)
Divided the total of sick leave days by the total working days to get the Sick Leave % :
SL % = DIVIDE([SL Count],[Total Working Days],0)
Here's the approach I used to show these metrics by month:
Created a "Month" column by extracting the month from the date column in Power Query
Used the months in a Slicer to filter all data on the page by each month
Here's the approach I used to answer what day most people are present and the trend:
Created a table to show the Presence % by day of the week.
Created an area chart to show the trend of when people are present:
Here's the approach I used to answer what day most people are working from home and the trend:
Created a table to show the working from home % by day of the week.
Created an area chart to show the trend of when people working from home:
Here's the approach I used to answer what day most people are on sick leave and the trend:
Created a table to show the sick leave % by day of the week.
Created an area chart to show the trend of when people are on sick leave:
Here's my approach to answer what employees are taking the most sick leave:
Created a table which highlights specific employee details
Within this table there are 2 key visualizations:
A highlighted column with a gradient to show what employees are taking the most sick leave.
A bar column showing each employee's Prescence.
Results & Key Takeaways
Weekly Insights
1. The most employees are present on Mondays, at 93.2%
2. The most employees work from home on Fridays, at 13%
3. The most employees take sick leave on Mondays, at 1.6%
Employee Trends
4. The employee work from home rate is slightly increasing, so it may be suitable to further accommodate a hybrid working model for employees.
Employees on sick leave is also rising, so it may be suitable to include some hygiene precautions in the office to reduce outbreaks.
5. Ayanna Atkins has taken the most sick leave at 10.7%, and has a prescence of 58.9%. So it may be suitable to contact her and find out why
