Data Analysis of HR data using Power BI

This project aims to analyze HR data using Power BI, transforming sample raw data from an Excel sheet into meaningful insights through data analysis and visualizations. The results of the analysis are documented for comprehensive understanding

Common Power BI terms

Measure: A measure is a dynamic calculation used to analyze and aggregate data. Measures are created using Data Analysis Expressions (DAX), a formula language for creating custom calculations in Power BI, Excel, and other data analysis tools. Measures are typically used to calculate sums, averages, minimum or maximum values, counts, or more complex calculations that can't be directly achieved with simple aggregation functions. In other words a measure is basically a variable that we will be creating in order to create my own formulas and expressions according to requirements.

All the measures used in this Data Analysis

1. Headcount = countrows(staff)
2. Average Salary = AVERAGE(staff[Salary])
3. Minimum Salary = min(staff[Salary])
4. Maximum Salary = MAX(staff[Salary])
5. Cumulative Headcount = VAR currentDate = LASTDATE(staff[Date of Join]) RETURN CALCULATE([Headcount], ALL(staff[Date of Join]), staff[Date of Join]<=currentDate)
6. Average Leave Balance = AVERAGE(staff[Leave Balance])
7. LBL over 20 days = CALCULATE([Headcount], staff[Leave Balance]>20)

The provided schema consists of the following fields, each containing specific information about employees:

1. Name: The name of the employee.
2. Emp ID: The unique identifier for the employee.
3. Gender: The gender of the employee.
4. Education Qualification: The educational qualification of the employee.
5. Date of Join: The date when the employee joined the organization.
6. Job Title: The job role or title of the employee.
7. Salary: The salary of the employee.
8. Age: The age of the employee.
9. Leave Balance: The remaining leave balance for the employee.

Types on Analysis, I will be performing on sample data :

1. How many people are in each job?
2. Gender Break down of the staff
3. Age spread of the staff
4. Which jobs pay more?
5. Top earner in each job
6. Qualification versus salary
7. Staff growth trend over time
8. Employees filter starting letter
9. Leave balance analysis
10. Quck HR dashboard

HR DashBoard Overview

Process

1. First step is to load our HR data in to POWER BI

2. Transform the data according to our adjustment - changed the headers to first columns and names the table as staff from data

3.Performing analysis

1. How many people are in each job? create a headcount , Headcount = countrows(staff) create a bar chart visualization with Job title on Y axis and Headcount on X axis

through this visualization we can see Packagin associate has most number of staff and while marketing specialist has lowest

2. Gender breakdown Analysis according to pie chart we can see there are 73 males 43.34% and 88 females 54.66%

   

adding slicer with job field in the title we can analyze how many males and females work in each department

3. Age spread Analysis

   -For analysis of age spread we will use column chart. -on X-axis i will put age -on y-axis i will headcount -create a age bin with 5 years age spread, for better visualization. -breaking it down in gender - putting gender in legend -according to analyzation of charts we can see there is 1 female in 2025 age categpry while most of the majority life between 35-45 with females 44 and male 41. -overall distribution of age between genders looks almost same but headcount of female is more

4. Which jobs Pay more

To do this we have to create a new measure to calculate the average of all staff salaries. Average Salary = AVERAGE(staff[Salary])

create a table with selecting job titles and average salary

Through this table we can see that Product manager has the highest average salary $82,825 and Packagin Associate has the lowest average $33,409. But Average is not signicant because we cannot see the headcount of each employee. Adding headcount to the table.

As we can see through our visualization, 16 people are working as Product Manager with average of 82,825 and Packaging Associate has 22 people Average is $33,409. Adding headcount to table represents the data better and making visualization justifiable.

Adding range to the table. create new measure minimum and maximum salary

1. Minimum Salary = min(staff[Salary])
2. Maximum Salary = MAX(staff[Salary])

Add Minimum and Maximum measures salaries to the table:

We can see the Maximum salary of an employee who is Packaging associate is $36,200 and minimum salary is $28,900. While the Maximum salary of an employee who is Product manager is $85,000 and minimum salary is $80,700.

Through this easy visualization breakdown we got to analyze the range, headcount, and average salary of employees in their respect job.

5. Top Earners in each job

   Create a headcount graph of job title and create a table selection titles of Name, Employee ID, Gender, and salary. Formate the table showing only top 3 people by their salary.

   

   These are the Top 3 People across the country with highest salary.

   We can select any titles from Job title graph and we can see top 3 people salary in that job.

   Top 3 People of Packaging associate

   Top 3 People of Profuction Operator

   Top 3 People who are Production Manager

6. Qualification versus Salary

   So, we are going to look at employees qualication and how much they are paid. To see the trend whether is salary is correlated to their qualications.

   We are going to create a scatter plot in order to show the correlation between salary and qualification.

   Note: Scatter plot doesn't work with Non-numerical data, so we are going to transform qualification into Ranks

   

   Transforming and assigning ranks to qualification.

   

   Assigned ranks

   

   We can see the salary spreads of different employees according to their qualifications.

7. Staff Growth trend over time

   To see a trend growth, A line chart perfect represents the growth over time.

   

   We can see in our Line chart tread, one line shows on each date how many people joined and the other trend line shows cumulative headcount of all the employee till that date starting from starting. We can also see the surgest of growth between particular dates by hovering over the trend line for example, From September 2018 to December 2018 more than 10 people joined the company.

8. Employee filter by starting letter

   Goal is to search the employee through their starting letter of their name.

   First we need to transform the data by extracting first letter from their name.

   We will creating a slicer chart through which we can select employee first letter in order to sort them to see and analyze their details. And we are going to also add a table with employee details as follows:

   

   In this visualization we can see everybody with their Employee ID, Name, Job Title because we have not selected any letter in the slicer chart.

   

   Every employee with G starting letter.

   

   Every Employee with S starting letter.

   Making more visual changes by adding a card visual with headcount through which we will know how many people are their with the starting letter we can see there are 6 people with H starting letter.

   Through this we can select and analyze date more easily.

9. Leave balance Analysis

   We are going to analyze average leave balance aswell as to count the number of employees that have excess leave balance of certain amount.

   Create measure: Average Leave Balance = AVERAGE(staff[Leave Balance]), This measure calculates average leave balance LBL over 20 days = CALCULATE([Headcount], staff[Leave Balance]>20), This measure calculate the number of people having more than 20 leave balance.

   Create a bar graph showing average leave balance based on the Job title

   Add the LBL over 20 days measure in the tooltip and if you hover over the bar chart and you can see the number of people in each job title with over 20 days Leave balance.

   

   Add gender graph to see based on gender

   

   Just by clicking on gender and hovering over the bar chart we can see more details based on gender and department.

10. Quick HR Dashboard

    Finally, we will create an HR Dashboard which represents visualizations of all the analysis of data based on Excel sheet. It has headcount, Average salary, Average leave balance, and other charts representing the overall analysis for better understanding the company employee structure and their details without looking at matrices.

    

Acknowledgment

This Data Analysis and Power BI visualization is inspired through YouTube channel called Chandoo. And sample HR data from Keggle.