Program 11.
Analysis of HR Dataset:
i) Create KPI to show employee count, attrition count, attrition rate, attrition count,
active employees, and average age.
ii) Create a Lollipop Chart to show the attrition rate based on gender category.
iii) Create a pie chart to show the attrition percentage based on Department
Category- Drag department into colours and change automatic to pie. Entire view,
Drag attrition count to angle. Label attrition count, change to percent, add total
also, edit label.
iv) Create a bar chart to display the number of employees by Age group,
v) Create a highlight table to show the Job Satisfaction Rating for each job role based
on employee count.
vi) Create a horizontal bar chart to show the attrition count for each Education
field Education field wise attrition – drag education field to rows, sum attrition
count to col,
vii) Create multiple donut chart to show the Attrition Rate by Gender
for different Age group. Solution :
i) Create KPI to show employee count, attrition count, attrition rate,
attrition count, active employees, and average age.
Step1: Create a New measure
Employee Count = COUNT('HR'[EmployeeNumber])
Step2: Choose KPI card in the visualization and drag and drop the Employee Count.
Format your visuals of your style.
Step3: Create a New Measure
Attrition Count = COUNTROWS(FILTER('HR', 'HR'[Attrition]="Yes"))
Step4: Choose KPI card in the visualization and drag and drop the Attrition
Count. Format your visuals of your style.
Dept. of ISE 78
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
Step5: Create a New Measure
Attrition Rate = DIVIDE([Attrition Count], [Employee Count], 0) * 100
Step6: Choose KPI card in the visualization and drag and drop the Attrition Rate.
Format your visuals of your style.
Step7: To find active employees create a new
measure Active Employees = [Employee
Count] - [Attrition Count]
Step8: Choose KPI card in the visualization and drag and drop the Active
Employees. Format your visuals of your style.
Step9: To calculate average age create a new
measure Average Age = AVERAGE(HR[Age])
Step10: Choose KPI card in the visualization and drag and drop the Average Age.
Format your visuals of your style.
ii)Create a Lollipop Chart to show the attrition rate based on gender category.
Power BI does not have a native Lollipop Chart, so you will simulate it using
(any chart) a Line and Stacked column Chart
Dept. of ISE 79
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
ii) Create a pie chart to show the attrition percentage based on Department
Category-
Drag department into colors and change automatic to pie. Entire view, Drag attrition
count to angle. Label attrition count, change to percent, add total also, edit label.
From the Visualizations pane on the right, select the Pie Chart visual icon.
This will add a blank pie chart to your report canvas.
Set Up the Pie Chart:
Drag the Department Field to the Legend area.
Drag the Attrition Count Measure to the Values area.
Configure Data Labels and Formatting:
Click on the Pie Chart to select it.
Open the Format Pane (paint roller icon).
Change Data Label Settings:
Go to the Data Labels section in the Format pane.
Toggle Data Labels to On.
In the Data Label settings, change Label Style to Percent. This will show
the percentage of each department's attrition relative to the total.
To show the Total alongside the percentages:
o Ensure that Data Labels are visible and set to Show.
o You can add a Total Label in the Title or Tooltips sections if needed
for additional context.
Format the Pie Chart:
Adjust Colors:
o Go to the Data Colors section in the Format pane.
o You can customize colors for each department by clicking on the color
next to the department name and choosing the color you prefer.
Edit Labels:
o If you want to customize the text in the labels, you can use the Data
Label formatting options to adjust font size, color, and display units.
Finalize Your Visualization:
Ensure your pie chart looks as expected with percentages representing the
attrition rate for each department.
Dept. of ISE 80
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
iii) Create a bar chart to display the number of
employees by Age group, Step1: right click Age
and choose new group and set bin size as 5.
Step2: Choose any bar chart drag and drop new age bin and employee count.
V) Create a highlight table to show the Job Satisfaction Rating for each job
role based on employee count.
Create a Matrix visual from the Visualizations pane.
Drag the Job Role field to Rows.
Drag the Job Satisfaction Rating field to Columns.
Drag the Employee Count measure to Values.
Dept. of ISE 81
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
VI) Create a horizontal bar chart to show the attrition count for each
Education field Education field wise
Attrition – drag education field to rows, sum attrition count to col,
Step1: Horizontal bar chart It's called the Clustered Bar Chart or Stacked Bar
Chart in the visualization pane
Choose stacked bar chart and set y axis is education filed and x axis is attrition
count.
vii) Create multiple donut chart to show the Attrition Rate by Gender for
different Age group. Choose donut chart and drag and drop legend as
gender and value as attrition rate.
1. Select the Donut Chart from the Visualizations pane.
2. Create separate Donut Charts for different age groups.
For each chart, filter the dataset based on age group (using the Age Group
Dept. of ISE 82
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])
field created earlier).
3. Drag the Gender field to Legend.
Drag the Attrition Rate measure to
Values
1. Repeat for each age group, ensuring each donut chart represents a different
age group with gender breakdown.
Note:
Use Filters to dynamically adjust visuals where necessary (e.g., filter by Age
Group or Education Field).
Dept. of ISE 83
Downloaded by Ballari Ramesh (ballariramesh0825@[Link])