Tableau Lesson 53 – HR Analysis | Dataplexa

Section IV — Lesson 53

HR Analysis Project

HR dashboards answer people questions — where is headcount growing, which departments are losing staff, how is salary distributed, and where are the retention risks. This project builds a complete workforce dashboard from a single employee table using five analytical views and six calculated fields.

The Dataset

A 30-row employee table with hire date, department, status, salary, tenure, and performance rating. Save as hr_data.csv.

hr_data.csv

Emp_ID,Name,Department,Status,Hire_Year,Salary,Tenure_Years,Performance,Gender
E001,Alice Chen,Engineering,Active,2019,95000,5,High,F
E002,Bob Marsh,Sales,Active,2021,62000,3,Medium,M
E003,Carol Patel,HR,Active,2020,58000,4,High,F
E004,David Kim,Engineering,Active,2018,108000,6,High,M
E005,Eva Rossi,Marketing,Left,2020,71000,3,Medium,F
E006,Frank Obi,Sales,Active,2022,55000,2,Low,M
E007,Grace Liu,Engineering,Active,2017,120000,7,High,F
E008,Henry Park,HR,Left,2021,54000,2,Low,M
E009,Iris Müller,Marketing,Active,2019,78000,5,High,F
E010,James Tran,Sales,Left,2022,50000,1,Low,M
E011,Karen Blake,Engineering,Active,2016,132000,8,High,F
E012,Liam Nkosi,Operations,Active,2020,67000,4,Medium,M
E013,Maya Singh,Marketing,Active,2021,74000,3,High,F
E014,Noah Hart,Operations,Left,2022,61000,1,Low,M
E015,Olivia Diaz,Sales,Active,2020,68000,4,Medium,F
E016,Paul Yuen,Engineering,Left,2021,89000,2,Medium,M
E017,Quinn Adams,HR,Active,2018,62000,6,Medium,F
E018,Rachel Brooks,Operations,Active,2019,72000,5,High,F
E019,Sam Okafor,Sales,Active,2021,60000,3,Medium,M
E020,Tina Vogel,Marketing,Left,2021,69000,2,Low,F
E021,Uma Patel,Engineering,Active,2020,97000,4,High,F
E022,Victor Leung,Operations,Active,2022,65000,2,Medium,M
E023,Wren Johansson,HR,Left,2022,52000,1,Low,F
E024,Xander Mills,Sales,Active,2019,72000,5,High,M
E025,Yara Hassan,Engineering,Active,2021,101000,3,High,F
E026,Zoe Carter,Marketing,Active,2020,77000,4,High,F
E027,Aaron Lee,Operations,Left,2021,63000,2,Low,M
E028,Beth Simmons,Sales,Left,2022,48000,1,Low,F
E029,Chris Varma,Engineering,Active,2022,88000,2,Medium,M
E030,Diana Fox,HR,Active,2020,60000,4,Medium,F

The Five Business Questions

#	Business Question	Technique	Chart
Q1	How is headcount distributed across departments and status?	COUNTD by dimension + stacked bar	Stacked bar chart
Q2	Which departments have the highest attrition rate?	Attrition Rate % (FIXED LOD)	Horizontal bar with reference line
Q3	How is salary distributed — are there outliers?	Box plot + average reference line	Box plot by department
Q4	Does higher tenure correlate with higher performance?	Scatter plot + trend line	Scatter plot
Q5	Is there a salary gap between genders in the same department?	Average salary by Gender + FIXED LOD gap calc	Side-by-side dot plot

Step 1 — Connect and Prepare

Connect to hr_data.csv. Verify types: Salary and Tenure_Years as Number (decimal), Hire_Year as Number (whole), all others as String.

Right-click Emp_ID in the Data pane → Default Properties → Comment: "Primary key — use COUNTD for headcount, not SUM or COUNT". This reminder prevents accidentally summing the ID field.

Create an extract: Data → Extract Data → Extract.

Step 2 — Build the Calculated Fields

Calc 1 — Headcount

COUNTD([Emp_ID])

COUNTD (count distinct) ensures each employee is counted once even if the dataset ever has duplicate rows. Always use COUNTD over COUNT for people counts.

Calc 2 — Attrition Rate % (FIXED LOD)

{FIXED [Department] :
  COUNTD(IF [Status] = "Left" THEN [Emp_ID] END)
} /
{FIXED [Department] :
  COUNTD([Emp_ID])
}

Two FIXED LODs: numerator counts employees who left per department, denominator counts all employees per department. The result is department attrition rate regardless of any filters applied to the view.

Calc 3 — Avg Salary by Gender (FIXED LOD)

{FIXED [Department], [Gender] : AVG([Salary])}

Calc 4 — Salary Gap (F minus M)

{FIXED [Department] : AVG(IF [Gender] = "F" THEN [Salary] END)}
-
{FIXED [Department] : AVG(IF [Gender] = "M" THEN [Salary] END)}

Positive = women earn more on average in that department. Negative = men earn more. Centred diverging colour palette makes the direction immediately readable.

Calc 5 — Performance Score (numeric)

IF [Performance] = "High" THEN 3
ELSEIF [Performance] = "Medium" THEN 2
ELSEIF [Performance] = "Low" THEN 1
END

Converts the text rating to a numeric value so it can be plotted on a scatter axis and averaged. Without this conversion, Performance cannot be placed on a quantitative axis.

Calc 6 — Retention Flag

IF [Status] = "Left" AND [Performance] = "High" THEN "High Risk Lost"
ELSEIF [Status] = "Left" THEN "Left"
ELSE "Active"
END

Flags employees who left despite high performance — these are the most costly attrition cases. Used to colour-code the scatter plot to highlight where the organisation lost its best people.

Step 3 — Chart 1: Headcount by Department and Status (Q1)

New sheet. Drag Department to Rows, Headcount to Columns. Mark type: Bar. Sort descending by Headcount.

Drag Status to the Colour shelf — bars split into Active (blue #0ea5e9) and Left (red #ef4444). Drag Headcount to Label. Remove grid lines.

Title: Sales Has the Most Departures — 4 of 8 Staff Left. Tab: Headcount.

Headcount by department — expected result

Step 4 — Chart 2: Attrition Rate by Department (Q2)

New sheet. Drag Department to Rows, Attrition Rate % to Columns. Mark type: Bar. Sort descending. Format axis as Percentage 0dp.

Drag Attrition Rate % to Colour. Set palette to Red Sequential — higher attrition = darker red. Add a reference line at 0.20 (20% industry benchmark) → label "Industry avg: 20%".

Add Attrition Rate % to Label. Title: Sales Attrition at 50% — More Than Double the Industry Average. Tab: Attrition Rate.

Step 5 — Chart 3: Salary Distribution Box Plot (Q3)

New sheet. Drag Department to Columns and Salary to Rows. Mark type: Circle. Drag Emp_ID to Detail — this disaggregates the view so each employee is one mark. You should now see 30 individual circles.

Click Analytics pane → drag Box Plot onto the view. Tableau draws the IQR box, median line, and whiskers over the individual circles.

Drag Status to Colour: Active = #0ea5e9, Left = #ef4444. Format Salary axis as $#,##0,\K. Title: Engineering Salaries Highest and Most Spread — Two Outliers Above $120K. Tab: Salary Distribution.

Box plot anatomy — annotated

Step 6 — Chart 4: Tenure vs Performance Scatter Plot (Q4)

New sheet. Drag Tenure_Years to Columns and Performance Score to Rows. Drag Emp_ID to Detail to disaggregate. Mark type: Circle. Set size to medium.

Drag Retention Flag to Colour: Active = #0ea5e9, Left = #94a3b8, High Risk Lost = #ef4444.

Analytics pane → drag Trend Line → Linear onto the view. The positive slope confirms longer-tenured employees trend toward higher performance. Drag Name to the Tooltip shelf for hover labels.

Fix the Performance Score y-axis: right-click → Edit Axis → Fixed Range 0.5 to 3.5. Add custom axis labels at 1 = Low, 2 = Medium, 3 = High via Format → Axis → Tick Marks. Title: Longer Tenure Correlates With Higher Performance — Red Dots Are Costly Losses. Tab: Tenure vs Performance.

Step 7 — Chart 5: Salary Gap by Department (Q5)

New sheet. Drag Department to Rows and Avg Salary by Gender to Columns. Drag Gender to Colour (F = #a855f7, M = #0ea5e9) and to Detail. Mark type: Circle. Increase mark size to large.

Drag Salary Gap to the Label shelf. Format as +$#,##0;-$#,##0 — positive (F earns more) shows green, negative (M earns more) shows red. Add Avg Salary by Gender to the Tooltip.

Format x-axis as $#,##0,\K. Title: Women Earn More in Engineering and Marketing — Gap Reversed in Sales. Tab: Salary Gap.

Step 8 — Assemble the Dashboard

New dashboard → Automatic size → background #f8fafc. Title: Workforce Analysis — 30 Employees, 22pt bold.

Row 1 (Horizontal): Headcount stacked bar (left 50%) + Attrition Rate (right 50%).

Row 2 (Horizontal): Salary Distribution box plot (left 40%) + Tenure vs Performance scatter (right 60%).

Row 3: Salary Gap dot plot — full width. Add a Department filter applied to all sheets. Set all container Outer Padding to 6. Add a Dashboard filter action: clicking a department bar in Headcount filters all other sheets.

📌 Teacher's Note

The Attrition Rate FIXED LOD is the most important calculation in this project. Without it, filtering the view to Active employees only would make the "Left" count drop to zero, making attrition look like zero percent. FIXED ignores view-level filters and calculates over the entire department regardless of what the user selects — so the rate stays honest even when the user drills into a subset. Any metric that measures absence or departure needs this approach: you must count the whole group in the denominator, not just what is visible in the view.

Practice Questions

1. The Headcount field uses COUNTD([Emp_ID]) instead of COUNT or SUM. What is the difference and why is COUNTD the correct choice for a headcount measure?

2. The box plot and scatter plot both need to show one mark per employee rather than one aggregated mark per department. How do you disaggregate the view in Tableau to achieve this?

3. A viewer filters the Headcount chart to show only Active employees. Why does the Attrition Rate % still show the correct percentage rather than dropping to zero?

Next up — Lesson 54: Retail Analysis Project — building a retail performance dashboard covering product sales, basket analysis, store comparison, and seasonal trends.

← Previous Course Index Next →

Tableau Course