People Analytics: SQL, Excel, Power BI

Build a 3-tool HR analytics pipeline with SQL, Excel, and Power BI.

Introduction

30 Second Summary

Every company tracks employee data like salaries, satisfaction scores, and tenure. When people start leaving, leadership wants to know who is leaving, why, and whether it can be predicted.

In this project, you will build a people analytics pipeline that mirrors how HR data teams actually work. You will query a database of 1,470 employees with SQL, build an interactive Excel dashboard for stakeholder reporting, then create a polished Power BI report for browser-based exploration.

What You'll Build

You will run a SQL query that instantly reveals which department has the highest attrition, click a slicer in Excel to filter an entire dashboard, and share a Power BI report link that stakeholders can explore in their browser.

By the end of this project, you'll have:

A SQLite database loaded with 1,470 employee records that you can query with joins, subqueries, and aggregations to instantly see attrition rates by department, tenure by job role, and salary distributions.
An interactive Excel dashboard with PivotTables, slicers, XLOOKUP-powered KPI cards, and conditional formatting that updates when you filter by department.
A polished Power BI report published in the browser with DAX measures and drill-down cross-filtering, where clicking any chart element filters the entire page.
Secret Mission: Add a DAX calculated column to flag high-risk employees (low satisfaction, overtime, short tenure) and build an attrition risk heatmap with color-scale conditional formatting in Power BI.

Are there any prerequisites?

You need Microsoft Excel for Mac installed on your computer. You also need a personal email address (Gmail, Outlook, or Yahoo) to set up a free Power BI trial during the project.

Before We Start

Before diving into SQL queries and dashboard building, let's lock in what you're about to build and why it matters. This is your chance to commit to the full picture before any hands-on work begins.

Set Up Your Data Environment

You have 1,470 rows of HR data waiting to be analyzed, but right now they exist as a CSV file on a website. Before you can write a single query, you need that data loaded into a database.

In this step, you will download the IBM HR Analytics dataset from Kaggle, set up a SQLite database on your Mac, and import the CSV so you have a queryable table of 1,470 employee records.

In this step, get ready to:

Download the IBM HR Analytics Employee Attrition CSV from Kaggle.
Create a project folder and set up a SQLite database.
Import the CSV into the employees table and verify the row count.

Download the dataset from Kaggle

The dataset you will use is the IBM HR Analytics Employee Attrition dataset. It is a fictional dataset created by IBM data scientists, containing 1,470 employee records with 35 attributes like department, monthly income, job satisfaction, and attrition status.

Open your web browser and navigate to the Kaggle dataset page:
https://www.kaggle.com/datasets/pavansubhasht/ibm-hr-analytics-attrition-dataset

Do I need a Kaggle account?

Yes. Kaggle requires a free account to download datasets. If you do not have one, click Register on the Kaggle page and sign up with your email or Google account. It takes about a minute.

Once signed in, click the Download button near the top of the dataset page.
Kaggle downloads a ZIP file called archive.zip to your Downloads folder.
Locate archive.zip in Finder and double-click it to extract the contents.
You should see a file called WA_Fn-UseC_-HR-Employee-Attrition.csv in the same folder.

Don't see the CSV after extracting?

Make sure you double-clicked the archive.zip file itself, not just the download notification. macOS extracts it into the same folder automatically. Look for WA_Fn-UseC_-HR-Employee-Attrition.csv inside your Downloads folder.

Still stuck? Help me find my downloaded Kaggle CSV file on macOS.

Create a project folder and verify SQLite

Now you need a dedicated folder to keep your database and data files together. You also need to confirm that SQLite is ready to use on your Mac.

Press Cmd+Space to open Spotlight search.
Type Terminal and press Enter to open it.

Create your project folder and move into it by running these commands:

mkdir ~/people-analytics
cd ~/people-analytics

Confirm the folder was created by running:

pwd

You should see /Users/your-username/people-analytics printed in the terminal.

Move the CSV you downloaded into your project folder by running:

mv ~/Downloads/WA_Fn-UseC_-HR-Employee-Attrition.csv ~/people-analytics/

Confirm the file arrived by running:

ls

You should see WA_Fn-UseC_-HR-Employee-Attrition.csv listed in the output.

File not found when running mv?

The CSV filename is long and easy to mistype. If Kaggle extracted the file with a slightly different name, check what is in your Downloads folder by running:

ls ~/Downloads/*.csv

Use the exact filename that appears in the output with the mv command. Help me move my Kaggle CSV into my project folder.

Now verify that SQLite is available on your Mac by running:

sqlite3 --version

✔️ I see a version number

SQLite is ready to go. Your Mac ships with it pre-installed, so no extra setup needed.

ⓧ Command not found

This is unusual on macOS since SQLite comes pre-installed. Try running the full path directly:

/usr/bin/sqlite3 --version

If that prints a version number, use /usr/bin/sqlite3 instead of sqlite3 for the rest of this project. If it still fails, help me get SQLite working on my Mac.

Import the CSV into SQLite and verify the row count

With the CSV in your project folder, you can now create a database and load the data. SQLite can auto-create a table from a CSV file, using the first row as column names.

Launch SQLite and create a new database file by running:

sqlite3 hr_analytics.db

What does this command do?

The sqlite3 command opens the SQLite shell. When you pass a filename like hr_analytics.db, SQLite creates that database file if it does not already exist. You are now inside the SQLite prompt, where commands start with a dot (.) for shell commands or use standard SQL.

Your terminal prompt changes to sqlite> to show you are inside the SQLite shell.

Set the import mode to CSV by entering:

.mode csv

Import the CSV into a new table called employees by entering:

.import WA_Fn-UseC_-HR-Employee-Attrition.csv employees

What does .import do here?

Because the employees table does not exist yet, SQLite reads the first row of the CSV as column names and creates the table automatically. Every subsequent row becomes a data record in the table. After this command, you have a fully queryable table with 35 columns and 1,470 rows of employee data.

Before you run the verification query, what number do you think SELECT COUNT(*) will return?

Verify that all rows were imported by running:

SELECT COUNT(*) FROM employees;

You should see 1470 printed. That is all 1,470 employee records from the IBM dataset, now sitting in your SQLite database.

Quickly check that the columns look correct by running:

.headers on
SELECT * FROM employees LIMIT 1;

You should see a single row of data with column headers like Age, Attrition, Department, MonthlyIncome, and more.

Seeing 0 rows or an error?

If SELECT COUNT(*) returns 0, the import may have failed silently. Make sure you ran .mode csv before the .import command. Without it, SQLite may misinterpret the file format.

If you see an error about the table already existing, you can drop it and re-import by running DROP TABLE employees; and then repeating the .mode csv and .import commands.

Still stuck? Help me import my CSV into SQLite on macOS.

Your database is loaded and ready. Next up, you will write SQL queries to dig into this data and surface workforce insights like attrition rates and salary distributions.

Query Workforce Data with SQL

Your database is loaded with 1,470 employee records spanning 35 attributes. That is a solid foundation, but scrolling through rows of raw data will not reveal the patterns hiding inside it.

SQL lets you ask precise questions and get instant answers. In this step, you will write queries that calculate attrition rates by department, find employees earning above their department average, and export those results to CSV files for your Excel dashboard.

In this step, get ready to:

Write aggregation queries to calculate department headcount, attrition rates, and average income.
Write a subquery to find employees earning above their department's average salary.
Export query results to CSV files for use in Excel.

Write aggregation queries for department metrics

The employees table holds data on every employee across three departments. Your first queries will summarize that data at the department level so you can compare workforce metrics at a glance.

Return to your Terminal and connect to your database by running these commands:

cd ~/people-analytics
sqlite3 hr_analytics.db

Already in the sqlite3 session?

If you still see the sqlite> prompt from Step 1, you are already connected. Skip the commands above and continue to the next instruction.

Turn on column headers and columnar formatting so your query results are easy to read, by running these commands:

.headers on
.mode column

What do these dot-commands do?

SQLite has special commands that start with a dot. .headers on prints column names above your query results. .mode column aligns the output in neat columns instead of pipe-separated values.

Your first query will answer a simple question: how many employees does each department have, and what is their average monthly income?

Calculate headcount and average monthly income by department by running this query:

-- Headcount and average income per department
SELECT
    Department,
    COUNT(*) AS Headcount,
    ROUND(AVG(MonthlyIncome), 0) AS AvgMonthlyIncome
FROM employees
GROUP BY Department;

What does this query do?

GROUP BY Department splits the 1,470 rows into one group per department, so each department gets its own summary row.
COUNT(*) counts how many employees belong to each group.
ROUND(AVG(MonthlyIncome), 0) calculates the average income for each department and rounds it to a whole number.

You should see three rows, one for each department: Human Resources, Research & Development, and Sales. Each row shows the headcount and average monthly income for that department.

Seeing an error instead of results?

Make sure you end the query with a semicolon. SQLite waits for more input until it sees one.
Check that column names match exactly: Department and MonthlyIncome are case-sensitive.
If you see no such table: employees, you may have opened a different database. Exit with .quit and re-run sqlite3 hr_analytics.db from inside your people-analytics folder.

Why is my SQLite query returning an error instead of department results?

Now you know the size and average pay of each department. The next question is more targeted: how many employees are leaving each department?

The Attrition column stores Yes or No for each employee. To count only the employees who left, you need a CASE expression inside your COUNT.

Before you run this next query, think about it: which department do you predict has the highest attrition rate, and why?

Calculate the attrition rate for each department by running this query:

-- Attrition count and rate per department
SELECT
    Department,
    COUNT(CASE WHEN Attrition = 'Yes' THEN 1 END) AS AttritionCount,
    COUNT(*) AS TotalEmployees,
    ROUND(COUNT(CASE WHEN Attrition = 'Yes' THEN 1 END) * 100.0 / COUNT(*), 1) AS AttritionRate
FROM employees
GROUP BY Department;

What does this query do?

CASE WHEN Attrition = 'Yes' THEN 1 END returns 1 for employees who left and NULL for everyone else. Wrapping it in COUNT() counts only the non-NULL values, giving you the attrition count.
Multiplying by 100.0 (not 100) forces decimal division so you get a percentage like 19.0 instead of a truncated 0.
ROUND(..., 1) rounds the result to one decimal place for a clean percentage.

You should see three rows showing Human Resources, Research & Development, and Sales. Each row displays the number of employees who left, the total headcount, and the attrition rate as a percentage.

What should I notice?

Compare the attrition rates across departments. Did your prediction match the results? Even a small dataset like this one surfaces clear differences between departments that would be invisible in a raw spreadsheet.

Find above-average earners with a subquery

Department-level summaries tell you the big picture. The next question drills deeper: which individual employees earn more than their department's average salary?

Answering this requires two steps in one query. First, calculate each department's average income. Then, compare every employee's salary against that average. SQL handles this with a subquery joined back to the main table.

Find employees earning above their department's average salary by running this query:

-- Employees earning above their department's average
SELECT
    e.EmployeeNumber,
    e.Department,
    e.JobRole,
    e.MonthlyIncome,
    dept_avg.DeptAvgIncome,
    e.JobSatisfaction
FROM employees e
JOIN (
    SELECT Department,
           ROUND(AVG(MonthlyIncome), 0) AS DeptAvgIncome
    FROM employees
    GROUP BY Department
) dept_avg ON e.Department = dept_avg.Department
WHERE e.MonthlyIncome > dept_avg.DeptAvgIncome
ORDER BY e.Department, e.MonthlyIncome DESC;

What does this query do?

The inner query (inside the parentheses) calculates the average MonthlyIncome for each department. This is the subquery.
JOIN connects every employee row to their department's average, so each employee now has a DeptAvgIncome column next to their own salary.
WHERE e.MonthlyIncome > dept_avg.DeptAvgIncome filters the results to only employees whose salary exceeds their department's average.
ORDER BY sorts the results by department first, then by income (highest earners at the top of each department group).

You should see a list of employees scroll through your Terminal. Each row shows the employee number, their department, job role, monthly income, the department average income, and their job satisfaction rating.

Query returning zero rows?

Check the alias after the closing parenthesis. The subquery must be aliased as dept_avg for the ON clause to reference it.
Make sure the WHERE clause uses > (greater than), not < (less than).

Why is my SQL subquery with JOIN returning no results?

Export query results to CSV files

Your queries surfaced real workforce insights, but the results only exist in your Terminal right now. To build an Excel dashboard from this data, you need to save the results as CSV files.

SQLite can redirect query output to a file using the .output command. Combined with .mode csv, it writes results in comma-separated format that Excel can open directly.

Export the department attrition results to a CSV file by running these commands:

-- Switch to CSV output mode
.mode csv

-- Send output to dept_attrition.csv
.output dept_attrition.csv

-- Re-run the department attrition query
SELECT
    Department,
    COUNT(CASE WHEN Attrition = 'Yes' THEN 1 END) AS AttritionCount,
    COUNT(*) AS TotalEmployees,
    ROUND(COUNT(CASE WHEN Attrition = 'Yes' THEN 1 END) * 100.0 / COUNT(*), 1) AS AttritionRate
FROM employees
GROUP BY Department;

What does this do?

.mode csv switches the output format from columnar display to comma-separated values.
.output dept_attrition.csv redirects all query results to a file instead of printing them to your Terminal. You will not see any output on screen after running the query.

Export the above-average earners results to a second CSV file by running these commands:

-- Send output to above_avg_earners.csv
.output above_avg_earners.csv

-- Re-run the above-average earners query
SELECT
    e.EmployeeNumber,
    e.Department,
    e.JobRole,
    e.MonthlyIncome,
    dept_avg.DeptAvgIncome,
    e.JobSatisfaction
FROM employees e
JOIN (
    SELECT Department,
           ROUND(AVG(MonthlyIncome), 0) AS DeptAvgIncome
    FROM employees
    GROUP BY Department
) dept_avg ON e.Department = dept_avg.Department
WHERE e.MonthlyIncome > dept_avg.DeptAvgIncome
ORDER BY e.Department, e.MonthlyIncome DESC;

Why re-run the same queries?

The earlier queries printed results to your Terminal for you to read. These export commands redirect the same results to files. SQLite does not save previous output, so you need to run each query again with .output pointed at the target file.

Reset the output back to your Terminal and exit SQLite by running these commands:

-- Reset output to Terminal
.output stdout

-- Exit SQLite
.quit

Before you check, what files do you expect to see in your project folder alongside the original CSV and database?

Confirm the CSV files were created by running:

ls ~/people-analytics

You should see dept_attrition.csv and above_avg_earners.csv listed alongside hr_analytics.db and your original WA_Fn-UseC_-HR-Employee-Attrition.csv.

CSV files missing from the list?

Make sure you ran .mode csv before the .output command. Without it, the file may still be created but with column-formatted text instead of comma-separated values.
Check that you ran the full SELECT query after each .output command. The .output command sets the destination, but only a query fills the file with data.
If the files are empty (0 bytes), re-open SQLite with sqlite3 hr_analytics.db and repeat the export commands from the beginning of this substep.

Why are my SQLite .output CSV files missing or empty?

Your SQL queries extracted the key workforce metrics from raw data. You now have two CSV files ready to power an interactive Excel dashboard. Next up, you will import these results into Excel and build PivotTables, charts, and KPI cards that stakeholders can explore with a single click.

Build an Excel Dashboard from Query Results

Your SQL queries exposed the patterns hiding in 1,470 employee records. Attrition rates, salary distributions, and department breakdowns are all calculated and exported as CSV files.

Numbers in a terminal don't convince stakeholders. Excel dashboards with PivotTables and slicers are the bread and butter of HR reporting. In this step, you'll transform your raw employee data into an interactive dashboard that answers workforce questions at a glance.

In this step, get ready to:

Import your employee data into Excel, format it as a Table, and build PivotTables summarizing attrition and income.
Create KPI cards using COUNTIFS, AVERAGEIFS, and XLOOKUP formulas, plus conditional formatting to flag high attrition.
Add PivotCharts and a Department slicer to make the entire dashboard interactive.

Import your employee data and build PivotTables

Your exported CSVs answered specific questions. PivotTables let stakeholders rearrange and explore the data themselves. To build them, you need the full employee dataset inside Excel.

Press Cmd+Space to open Spotlight.
Type Excel and press Enter to open Microsoft Excel.
Click File in the menu bar, then click Open.
Navigate to the people-analytics folder on your Desktop.
Select WA_Fn-UseC_-HR-Employee-Attrition.csv and click Open.

You should see a spreadsheet with 1,470 rows of employee data across 35 columns, starting with Age in column A.

Raw CSV data doesn't give Excel the structure it needs for PivotTables and dynamic formulas. Formatting it as a Table adds filter dropdowns, structured column references, and automatic expansion.

Click any cell in the data (for example, cell A1).
Press Cmd+T to open the Create Table dialog.
Confirm that My table has headers is checked.
Click OK.

Your data now has banded rows and filter dropdowns on every column header.

Click the Table Design tab in the ribbon.
In the Table Name field on the left side of the ribbon, replace the default name with Employees.
Press Enter.

Why rename the Table?

You can now reference columns by name, like Employees[Attrition], instead of cell ranges like R2:R1471. This makes every formula you write self-documenting and immune to row-count changes.

Press Cmd+S to save.
Excel prompts you to save as .xlsx since CSV does not support Tables.
Change the file name to hr_dashboard.
Confirm the save location is your people-analytics folder.
Select Excel Workbook (.xlsx) as the format and click Save.
Double-click the sheet tab at the bottom (it likely shows a truncated CSV filename) and rename it to Data.

Your Data sheet now has 1,470 employee records formatted as a Table called Employees with filter dropdowns on every column header.

Your first PivotTable answers the question: which departments are losing the most people?

Click any cell inside the Employees Table on the Data sheet.
Click Insert in the menu bar.
Click PivotTable.
In the dialog, select New Worksheet and click OK.

Excel creates a new sheet with an empty PivotTable and the PivotTable Fields pane on the right.

In the PivotTable Fields pane, drag Department to the Rows area.
Drag EmployeeNumber to the Values area. Excel automatically sets the aggregation to Count.
Drag Attrition to the Columns area.

Your PivotTable now shows a cross-tab with three department rows (Human Resources, Research & Development, Sales) and columns for "No" and "Yes" attrition counts.

Double-click the sheet tab and rename it to AttritionPT.

Your second PivotTable answers a different question: how does average monthly income vary across job roles?

Click the Data sheet tab to switch back.
Click any cell inside the Employees Table.
Click Insert, then PivotTable.
Select New Worksheet and click OK.
Drag JobRole to the Rows area.
Drag MonthlyIncome to the Values area.
Click the dropdown arrow on Sum of MonthlyIncome in the Values area.
Select Value Field Settings.
Change the aggregation from Sum to Average and click OK.
Double-click the sheet tab and rename it to IncomePT.

Your PivotTable now shows the average monthly income for each of the nine job roles. Managers and Research Directors should appear with the highest averages.

Press Cmd+S to save your workbook.

PivotTable Fields pane not showing?

Click anywhere inside the PivotTable to activate it. The Fields pane only appears when a PivotTable cell is selected.
If it still doesn't appear, click PivotTable Analyze in the ribbon, then click Field List.

Help me troubleshoot my PivotTable Fields pane not appearing in Excel for Mac.

Create KPI cards with formulas and conditional formatting

PivotTables are powerful for exploration, but stakeholders want headline numbers at a glance. KPI cards give them exactly that. You'll build a Dashboard sheet with summary formulas and visual flags for high attrition.

Click the + icon next to the sheet tabs at the bottom of the workbook to add a new sheet.
Double-click the new sheet tab and rename it to Dashboard.
In cell B2, type Total Employees.
In cell B3, type Attrition Count.
In cell B4, type Attrition Rate (%).
In cell B5, type Avg Monthly Income (Leavers).

Click cell C2 and enter this formula to count every employee in the Table:

=COUNTA(Employees[EmployeeNumber])

Cell C2 should display 1470.

Click cell C3 and enter this COUNTIFS formula to count employees who left:

=COUNTIFS(Employees[Attrition],"Yes")

What does COUNTIFS do?

COUNTIFS counts rows that match one or more criteria. Here, it scans the Attrition column and counts every row where the value is Yes. This gives you the total number of employees who left the company.

Cell C3 should display 237.

Click cell C4 and enter this formula to calculate the attrition rate as a percentage:

=ROUND(C3/C2*100,1)

Cell C4 should display 16.1, meaning 16.1% of employees left.

Click cell C5 and enter this AVERAGEIFS formula to find the average monthly income of employees who left:

=ROUND(AVERAGEIFS(Employees[MonthlyIncome],Employees[Attrition],"Yes"),0)

What does AVERAGEIFS do?

AVERAGEIFS calculates the average of a range but only for rows that match your criteria. Here, it averages MonthlyIncome only for employees where Attrition equals Yes.

Compare this number to the overall average income to see whether compensation might be a factor in attrition.

Cell C5 should display a number around 4787.

Now build a department breakdown table so you can flag departments with high attrition and demonstrate XLOOKUP.

In cell B7, type Department. In C7, type Headcount. In D7, type Attrition. In E7, type Rate (%).
In cell B8, type Human Resources.
In cell B9, type Research & Development.
In cell B10, type Sales.

Click cell C8 and enter this formula to count employees in each department:

=COUNTIFS(Employees[Department],B8)

Select cell C8, then drag the fill handle (the small square at the bottom-right corner of the cell) down to C10 to copy the formula for all three departments.
Click cell D8 and enter this formula to count attrition per department:

=COUNTIFS(Employees[Department],B8,Employees[Attrition],"Yes")

Drag the fill handle from D8 down to D10.
Click cell E8 and enter this formula to calculate the attrition rate per department:

=ROUND(D8/C8*100,1)

Drag the fill handle from E8 down to E10.

You should see three departments with their attrition rates populated. Human Resources should show the highest rate.

Now add an XLOOKUP formula that lets you type any department name and instantly pull its attrition rate.

In cell B12, type Look up department:.
In cell C12, type Sales.
In cell B13, type Attrition Rate (%).
Click cell C13 and enter this XLOOKUP formula:

=XLOOKUP(C12,B8:B10,E8:E10,"Not found")

What does XLOOKUP do here?

XLOOKUP searches for the value in C12 ("Sales") inside the range B8:B10 (your department names). When it finds a match, it returns the corresponding value from E8:E10 (the attrition rate column).

Try changing C12 to Human Resources and watch the attrition rate update instantly.

Cell C13 should display the Sales department's attrition rate.

Now apply conditional formatting so departments with attrition above 15% stand out visually.

Select cells E8:E10 (the attrition rate column in your department table).
Click Home in the ribbon.
Click Conditional Formatting.
Click Highlight Cell Rules, then Greater Than.
In the value field, type 15.
Choose Light Red Fill with Dark Red Text from the format dropdown.
Click OK.

Any department with an attrition rate above 15% is now highlighted in red. You should see at least one department flagged.

Press Cmd+S to save.

Formulas showing #REF! or #NAME?

Make sure you renamed the Table to Employees on the Data sheet. If the Table still has its default name (like Table1), the structured references won't resolve.
Check that column names like Attrition, Department, and MonthlyIncome match the CSV headers exactly.

Help me fix formula errors on my Excel Dashboard sheet.

Add PivotCharts and connect the Department slicer

Charts turn PivotTable numbers into visuals that stakeholders can read in seconds. A slicer adds an interactive filter button that controls every PivotTable and chart at once.

Click the AttritionPT sheet tab.
Click any cell inside the PivotTable.
Click PivotTable Analyze in the ribbon, then click PivotChart.
Select Bar as the chart type and click OK.

A bar chart appears showing attrition counts by department.

Click the chart to select it.
Click the Chart Design tab in the ribbon.
Click Move Chart.
Select Object in: and choose Dashboard from the dropdown.
Click OK.

The bar chart now lives on your Dashboard sheet. Repeat the process for the income PivotTable.

Click the IncomePT sheet tab.
Click any cell inside the PivotTable.
Click PivotTable Analyze, then PivotChart.
Select Column as the chart type and click OK.
Click the chart, then click Chart Design in the ribbon.
Click Move Chart.
Select Object in: and choose Dashboard.
Click OK.

Your Dashboard sheet now has two charts: a bar chart for department attrition and a column chart for income by job role.

Now add a Department slicer that filters both PivotTables at once.

Click the AttritionPT sheet tab.
Click any cell inside the PivotTable.
Click PivotTable Analyze in the ribbon.
Click Insert Slicer.
Check the box next to Department and click OK.

A Department slicer button panel appears on the AttritionPT sheet. Move it to the Dashboard.

Click the slicer border to select it.
Press Cmd+X to cut.
Click the Dashboard sheet tab.
Press Cmd+V to paste the slicer onto the Dashboard.

Why connect the slicer to both PivotTables?

By default, the slicer only controls the PivotTable it was created from. You need to connect it to the second PivotTable so that one slicer click filters every chart on the dashboard.

Right-click the slicer border on the Dashboard sheet.
Click Report Connections.
Check the box next to the IncomePT PivotTable. The AttritionPT PivotTable should already be checked.
Click OK.

Before you test the slicer, predict what will happen. If you click "Sales" in the Department slicer, which charts do you expect to change?

Click Sales in the Department slicer.

Both PivotCharts update to show Sales-only data. The bar chart shows only the Sales department attrition count. The column chart shows average income for Sales job roles only. Your KPI formulas in rows 2-5 continue to show overall company metrics as a benchmark reference.

Why don't the KPI formulas change?

The COUNTIFS and AVERAGEIFS formulas reference the full Employees Table directly. They always calculate from the complete 1,470-row dataset regardless of slicer state.

In professional dashboards, this is intentional. Formula-based KPIs provide the overall benchmark while the slicer-controlled PivotCharts let stakeholders drill into specific departments. The combination gives context alongside detail.

Click the clear filter button (the funnel icon with an X) in the top-right corner of the slicer to remove the Sales filter.
Press Cmd+S to save your workbook.

Slicer not filtering both charts?

Right-click the slicer and check Report Connections. Both PivotTables must have their checkboxes selected.
If you don't see Report Connections in the right-click menu, try clicking the Slicer tab in the ribbon instead. The Report Connections button appears there as well.
Make sure both charts were moved to the Dashboard sheet. If a chart is still on its PivotTable sheet, it will update but you won't see the change from the Dashboard.

Help me connect my slicer to multiple PivotTables.

Your Excel dashboard is ready. Stakeholders can click a department in the slicer and instantly see how that department's attrition and income compare. Next up, you'll recreate this dashboard in Power BI, where you can publish it to the web and share it with anyone through a browser link.

Set Up Power BI and Import Your Data

Your Excel dashboard is a powerful reporting tool, but it lives on your computer as a local file. If a stakeholder wants to explore the data, you have to send them the workbook.

Power BI solves this by letting you publish interactive reports to the web. Stakeholders open a link in their browser and drill into the data themselves. In this step, you will create a free Azure account, sign into the Fabric portal, import your Excel data, and write your first DAX measure.

In this step, get ready to:

Create a free Azure account and sign into the Fabric portal with the 60-day free trial activated.
Upload your Excel workbook and connect the employees table as a semantic model.
Create a DAX measure that calculates attrition rate across the dataset.

Create a free Azure account and activate the Fabric trial

Power BI requires a work or school email to sign up directly. Since you are using a personal email, you will go through the official Azure path: create a free Azure account, set up a user in Microsoft Entra ID, then sign into the Fabric portal with that user.

Open your browser and go to the Azure free account page:
https://azure.microsoft.com/pricing/purchase-options/azure-account

Click Try Azure for free.
If you already have an Azure account, sign in with it. Otherwise, click Sign up and register using your personal email address.
Follow the prompts to complete your setup. You may be asked to verify your identity with a phone number and provide a payment method.

Will I be charged?

Microsoft uses the payment method for identity verification only. You will not be charged unless you explicitly upgrade to a paid subscription.

Once registration is complete, you land in the Azure portal. Now you need to create a user identity that Power BI can use.

In the Azure portal search bar at the top, type Microsoft Entra ID and select it from the results.
In the left menu, expand Manage and select Users.
Click New user, then Create new user.
Enter a display name and a user principal name (UPN). Set a password for the user. You do not need to set role assignments or other properties.
Click Create to finish.
After the user is created, open the Users pane and copy the user principal name (UPN). You will need this to sign in to Fabric.

What is a user principal name?

A UPN looks like an email address (for example, yourname@yourdomain.onmicrosoft.com). It is the identity you will use to sign into the Fabric portal.

With the UPN and password ready, you can now sign into Fabric and activate the free trial.

Open a new browser tab and go to app.fabric.microsoft.com.
Sign in using the UPN and password you just created.
Complete any authentication prompts. You may be asked to set up two-factor authentication using the Microsoft Authenticator app.
Once signed in, click your profile icon in the upper-right corner.
Select Free trial.
Choose a capacity region and click Activate.

You should see the Fabric home page with a confirmation that your 60-day free trial is active.

Not seeing the Free trial option?

Make sure you signed in with the Entra ID user you created (the UPN), not your personal email address. The trial is tied to the organizational identity.

help me troubleshoot why I can't see the Free trial option in the Fabric portal

Upload your Excel workbook and connect the employees table

Now that your Fabric workspace is ready, you can bring your HR data into Power BI. You will create a semantic model from your Excel workbook, which imports the data into Power BI so you can build reports on top of it.

In the Fabric portal, make sure you are in My workspace (click Workspaces in the left sidebar, then select My workspace).
Click New item at the top of the page.
On the selection pane that opens, choose Semantic model.
Select Excel as your data source.
Browse to the people-analytics folder on your Desktop and select hr_dashboard.xlsx.
When prompted to choose which tables to import, select the employees table (the main data table with 1,470 rows).

What is a semantic model?

A semantic model is Power BI's name for a structured dataset that you can build reports on. When you import your Excel data, Power BI creates a semantic model that holds all the rows and columns in memory.

Think of it as the foundation layer: visuals and measures all pull from this model.

When asked whether to create a report or just the model, select Create a semantic model only.

You land in the model editor. You should see the employees table listed in the Data pane on the right with all 35 columns visible.

Don't see the employees table?

If the table is missing, check that you selected the correct sheet when importing. In Excel, the data must be formatted as a Table (not just a range) for Power BI to detect it. Return to your hr_dashboard.xlsx in Excel, select the data range, click Format as Table, save, and re-import.

help me troubleshoot why Power BI is not detecting my employees table from the Excel workbook

Next, verify that Power BI assigned the correct data types to your columns.

In the model editor, click on the employees table in the Data pane to select it.
Expand the table to see the list of columns.
Click on a column name (for example, Age) and check the Properties pane. Confirm the Data type field shows Whole Number.
Check MonthlyIncome and confirm it shows Whole Number as well.
Check Attrition and confirm it shows Text (its values are Yes and No).
If any data type is incorrect, click the Data type dropdown in the Properties pane and change it to the correct type.

Spot-check a few key columns

You do not need to verify all 35 columns one by one. Power BI auto-detects types well. Focus on the columns you will use in measures and visuals: Attrition, MonthlyIncome, Department, JobRole, and JobSatisfaction.

Create a DAX measure for attrition rate

Your semantic model has the raw data, but it does not yet know how to calculate attrition rate. A DAX measure is a formula that Power BI evaluates on the fly whenever you use it in a visual. Unlike a cell formula in Excel, a DAX measure recalculates automatically based on whatever filters are active in the report.

In the model editor, make sure the employees table is selected in the Data pane.
Click the New measure button in the ribbon at the top.

A formula bar appears at the top of the editor. Type the following DAX formula into it:

Attrition Rate = DIVIDE(CALCULATE(COUNTROWS(employees), employees[Attrition] = "Yes"), COUNTROWS(employees))

What does this formula do?

COUNTROWS(employees) counts the total number of employee rows visible under the current filter context.
CALCULATE(COUNTROWS(employees), employees[Attrition] = "Yes") counts only the rows where Attrition is Yes.
DIVIDE divides those two numbers safely. If the denominator is zero, it returns blank instead of an error.
Because this is a measure, the result updates automatically when a slicer or filter changes the visible rows.

Press Enter to commit the formula.
Expand the employees table in the Data pane. You should see Attrition Rate listed as a measure (it has a small calculator icon next to its name).

Seeing a syntax error?

Double-check that you typed the table name employees exactly as it appears in the Data pane (case matters). Make sure the column reference employees[Attrition] uses square brackets around the column name. Verify that "Yes" is wrapped in double quotes.

help me fix a DAX syntax error in my Attrition Rate measure

Before you move on, do a quick check that everything is connected. What do you expect to see if you look at your workspace content list right now?

Click My workspace in the left sidebar to return to the workspace content list.

You should see your semantic model listed. Click back into it by selecting Open semantic model from its context menu (the ... icon). Confirm that the employees table is visible in the Data pane with your Attrition Rate measure listed under it.

Your data is now in Power BI with a working attrition rate measure. Next up, you will turn this model into an interactive report with card visuals, charts, and cross-filtering that goes far beyond what your Excel dashboard can do.

Build an Interactive Power BI Report

In the previous step, you imported your dataset into Power BI and built a semantic model with a DAX measure for attrition rate. The model is ready, but stakeholders cannot see any of those numbers yet.

A data model without visuals is invisible. In this step, you will turn your measures and fields into an interactive report with charts, cards, and cross-filtering that goes beyond what your Excel dashboard can do.

In this step, get ready to:

Create card visuals and a donut chart showing headline workforce metrics.
Add a bar chart and matrix visual with conditional formatting.
Add a department slicer and enable cross-filtering across all visuals.

Create card visuals and a donut chart

The first things a stakeholder looks for are headline numbers. Card visuals display a single metric in large text so viewers can see total headcount, attrition rate, and average income at a glance.

Return to your workspace at app.fabric.microsoft.com and find your semantic model (it shares the name of your uploaded hr_dashboard.xlsx file).
Click the More options menu (...) next to the semantic model and select Create report.

A blank report canvas opens with the Visualizations pane on the right and the Data pane listing your employees table fields.

In the Visualizations pane, select the Card visual icon.
From the Data pane, expand the employees table.
Drag EmployeeNumber into the Values field well.
Click the dropdown arrow on the field in the Values well and change the aggregation to Count.

The card now shows 1470. This is your Total Employees card.

Click an empty area of the canvas to deselect the card.
Select the Card visual icon again to create a second card.
Drag the Attrition Rate measure (the DAX measure you created in Step 4) into the Values well.

The card displays the attrition rate as a decimal. You should see a value around 0.16 (16% attrition).

Click an empty area of the canvas again to deselect.
Create a third card visual.
Drag MonthlyIncome into the Values well.
Change the aggregation to Average.

You now have three card visuals across the top of your canvas showing Total Employees, Attrition Rate, and Average Monthly Income.

What are card visuals?

A card visual displays a single number prominently. It is the Power BI equivalent of the KPI cards you built in Excel with XLOOKUP and COUNTIFS.

The difference is that Power BI cards automatically respond to filters and cross-filtering from other visuals on the page.

Click an empty area of the canvas to deselect everything.
In the Visualizations pane, select the Donut chart icon.
Drag Attrition to the Legend field well.
Drag EmployeeNumber to the Values field well, then change its aggregation to Count.

The donut chart shows two slices: Yes (employees who left) and No (employees who stayed).

Position the donut chart next to the card visuals by dragging it on the canvas.

What does this donut chart show?

The Attrition column contains Yes or No for each employee. By counting EmployeeNumber and splitting by Attrition, the donut shows the proportion of employees who left versus those who stayed.

Don't see the Attrition Rate measure?

The measure only appears if you created it in Step 4. If it is missing, return to the semantic model editor and add the DAX measure: Attrition Rate = DIVIDE(CALCULATE(COUNTROWS(employees), employees[Attrition] = "Yes"), COUNTROWS(employees)).

Help me find my Attrition Rate measure in Power BI.

Add a bar chart and matrix visual with conditional formatting

Cards and the donut chart give headline numbers. Now you need visuals that let viewers compare values across categories.

Click an empty area of the canvas to deselect.
In the Visualizations pane, select the Clustered bar chart icon.
Drag Department to the Y-axis field well.
Drag EmployeeNumber to the X-axis field well and change its aggregation to Count.
Drag Attrition to the Legend field well.

The bar chart shows three bars (Human Resources, Research & Development, Sales) split by attrition status. You can see at a glance which department has the most leavers.

Why a clustered bar chart?

A clustered bar chart places categories on the vertical axis and values on the horizontal axis. This makes it easy to compare counts across departments.

The Legend field splits each bar into Yes/No segments so you can see attrition alongside total headcount.

Click an empty area of the canvas to deselect.
In the Visualizations pane, select the Matrix visual icon.
Drag JobRole to the Rows field well.
Drag JobSatisfaction to the Values field well and change its aggregation to Average.

The matrix now lists each job role with its average satisfaction score. Resize the visual so all nine job roles are visible.

With the matrix visual selected, go to the Visualizations pane.
In the Values field well, click the dropdown arrow next to Average of JobSatisfaction.
Select Conditional formatting, then select Background color.
In the dialog that opens, set Format style to Gradient.
Set the Minimum color to a light red and the Maximum color to a light green.
Click OK to apply.

The matrix cells now show a color gradient from red (lower satisfaction) to green (higher satisfaction).

What is conditional formatting in a matrix?

Conditional formatting applies background colors to matrix cells based on their values. The gradient style maps a color range from the lowest value to the highest value.

This is the same idea as the conditional formatting you applied in your Excel dashboard. Power BI applies it dynamically as filters change.

Don't see Conditional formatting in the dropdown?

Make sure you are clicking the dropdown arrow on the field inside the Values well of the Visualizations pane, not in the Data pane. Conditional formatting is only accessible from the field well dropdown.

Help me find the Conditional formatting option in my matrix visual.

Add a department slicer and enable cross-filtering

Your report has six visuals, but they are not yet connected. A slicer lets the viewer pick a department and watch every visual update at once.

Click an empty area of the canvas to deselect.
In the Visualizations pane, select the Slicer visual icon.
Drag Department from the Data pane into the slicer's Field well.
Position the slicer along the left or top edge of the canvas so it is easy to find.

The slicer displays three department options: Human Resources, Research & Development, and Sales.

Click Sales in the slicer.

All six visuals immediately update to show Sales-only data. The Total Employees card drops and the attrition rate changes.

Click Sales again to deselect and restore the full view.

What is a slicer?

A slicer is a visual filter control that sits directly on the report canvas. It works like the slicers you connected to your Excel PivotTables, but in Power BI it automatically filters every visual on the page without any manual connection steps.

By default, Power BI cross-highlights visuals when you click a data point. To switch to cross-filtering (which removes unrelated data instead of dimming it), you use the Edit interactions controls.

Select the clustered bar chart on your canvas to make it active.
In the report menu bar, click Visual interactions.
Turn on the Edit interactions toggle.

Small filter and highlight icons appear on every other visual. These let you choose whether the selected visual cross-filters or cross-highlights each other visual.

For each of the other visuals (cards, donut, matrix), click the Filter icon (not None).
Select each remaining visual on the canvas one at a time, then verify the filter icons on the other visuals are set to filter mode.
Turn off the Edit interactions toggle when you are done.

What is cross-filtering?

Cross-filtering means clicking a data point in one visual removes unrelated data from other visuals. Cross-highlighting dims unrelated data instead of removing it.

By setting all visuals to filter mode, clicking the Research & Development bar in the department chart forces every other visual to show only R&D data.

In the report menu bar, select File, then Save.
When prompted, enter the name HR Analytics Report and click Save.

Before you test the cross-filtering, take a moment to predict what will happen. If you click the Research & Development bar in the department chart, what do you expect to see in the other visuals?

Click the Research & Development bar in the clustered bar chart.

Every other visual on the page cross-filters to show only R&D data. The Total Employees card drops to the R&D headcount and the Attrition Rate card updates in real time.

The donut chart resizes to show only R&D attrition and the matrix filters down to R&D job roles.

Click an empty area of the bar chart to clear the filter and restore the full view.

Why does this matter for interviews?

This is the core Power BI skill the job description asks for. When a hiring manager clicks a department and watches every metric update instantly, they see that you understand data modeling, DAX measures, and interactive report design.

Your Excel dashboard required separate slicers and manual connections. Power BI does it automatically.

Clicking a bar does not filter other visuals?

Make sure you turned on Edit interactions and set each visual's interaction to the Filter icon (not None). Also check that the bar chart and other visuals all use fields from the same employees table. If they reference different tables, Power BI cannot cross-filter between them.

Help me troubleshoot cross-filtering in my Power BI report.

Your Power BI report is live with card visuals, a donut chart, a bar chart, a matrix with conditional formatting, a department slicer, and cross-filtering connecting them all. Next up, take on an optional Secret Mission to push your report even further.

Secret mission

Build an Attrition Risk Heatmap

Your Power BI report shows who already left. Stakeholders want to know who might leave next. In this secret mission, you will create a DAX calculated column to flag high-risk employees, add a Risk Employees card, and build a color-coded heatmap matrix that reveals where attrition risk concentrates across departments and job roles.

Clean Up Your Resources

The Power BI workspace and Azure free account used in this project are part of a 60-day free Fabric trial. No charges apply during the trial period. Your SQLite database and Excel files are local to your Mac and cost nothing to keep.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Cost warning

The Fabric trial lasts 60 days. After it expires, non-Power BI Fabric items become inactive, but Power BI items in your personal workspace remain accessible with a free license.

Your Azure free account will not charge you unless you explicitly upgrade to pay-as-you-go. No action is required to avoid charges during the trial period.

Resources you used:

Local project folder (~/people-analytics) containing your SQLite database, CSV files, and Excel workbook.
Power BI workspace at app.fabric.microsoft.com with your HR Analytics Report and semantic model.
Azure free account and Microsoft Entra ID user created during Power BI setup.

Keep everything running

No action needed. Choose this if you are still actively exploring your report or plan to extend it further.

Your Power BI report stays accessible in the browser for the remaining Fabric trial period.
Your local files (hr_analytics.db, hr_dashboard.xlsx, CSV exports) remain on your Mac at no cost.
SQLite is pre-installed on macOS, so there is nothing extra to uninstall.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files and cloud workspace stay intact so you can return later.

Close any open Terminal sessions running SQLite.
Close your Excel workbook and any open browser tabs for app.fabric.microsoft.com.
Your Power BI workspace and report are saved automatically. You can reopen them anytime within the 60-day Fabric trial window.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below to clean up completely.

Delete Power BI workspace contents

Open app.fabric.microsoft.com in your browser and navigate to your workspace.
For each item listed (report, semantic model, dataset), click More options (...) next to the item, then select Delete.
Confirm the deletion when prompted.
To remove the workspace itself, open Workspace settings, select Other, then Remove this workspace.

Cancel your Azure subscription

Sign in to the Azure portal at portal.azure.com.
Navigate to Cost Management + Billing in the left menu.
Select Subscriptions, then click on your subscription.
At the top of the page, select Cancel.
Select a reason for cancellation, then click Cancel subscription to confirm.

Delete local project files

Delete the entire project folder by running this command in Terminal:

rm -rf ~/people-analytics

Confirm the folder is gone by running:

ls ~/Desktop

You should no longer see people-analytics in the output.

Nice Work!

You just built a complete people analytics pipeline that mirrors how HR data analysts actually work. Raw employee data went into a database, got queried with SQL, visualized in Excel, and published as an interactive Power BI report anyone can explore in a browser.

You've learned how to:

Queried a SQLite database of 1,470 employees using SQL aggregations, subqueries, and joins to calculate attrition rates by department and identify above-average earners by role.
Built an interactive Excel dashboard with PivotTables, PivotCharts, slicers, XLOOKUP-powered KPI cards, and conditional formatting that updates with a single click.
Published a Power BI report in the browser with DAX measures, card visuals, matrix visuals, and cross-filtering that lets stakeholders drill from company-wide metrics down to individual departments.
Completed an optional Secret Mission to push your analytics skills further.

Ready to quiz yourself?