"We are committed to diversity."Every company says this. But very few can prove it with data.
During a specialized project for PwC Switzerland, I was tasked with a challenge that faces almost every major corporation: The Executive Gender Gap. The HR department had gigabytes of raw employee data but zero insights. They knew they had a problem, but they didn't know where the leak was.
Was it hiring? Was it promotions? Or was it turnover? Here is how I used Power BI and DAX to turn a boring Excel spreadsheet into a dynamic "Diversity Engine."
1. The Problem: "Data Rich, Insight Poor"
HR data is notoriously messy. For this project, I was given a dataset containing 500+ employee records including ID, Gender, Job Level, Last Promotion Date, and Performance Rating.
The stakeholders didn't want a table. They wanted to answer three specific questions:
The Balance
What is the % of women at the Executive Level?
The Trend
Are we hiring more women this year than last year?
The Turnover
Are women leaving faster than men?
2. Data Modeling: Building the Star Schema
Before visualizing, I had to clean the data in Power Query. HR data often contains sensitive PII (Personally Identifiable Information), so the first step was anonymization.
The HR Data Model
Fact Table: Employee Performance & Salary
Dim 1: Employee Details (ID, Gender, Age Group)
Dim 2: Job Role (Manager, Director, VP)
Dim 3: Date Table (Hiring Year, Promotion Year)
3. The Logic: Advanced DAX for HR
Calculating a simple "%" is easy. But calculating "Diversity at the Executive Level" requires filtering context. Here is the custom DAX I wrote to isolate the leadership team.
DIVIDE(
CALCULATE(
COUNTROWS(Employees),
Employees[Gender] = "Female",
Employees[Job_Level] IN {"Director", "Executive"}
),
CALCULATE(
COUNTROWS(Employees),
Employees[Job_Level] IN {"Director", "Executive"}
),
0
)
I also needed to track Turnover Rate, which is critical for retention strategies.
DIVIDE(
COUNTROWS(Leavers),
([Headcount_Start] + [Headcount_End]) / 2,
0
)
4. The Final Dashboard
The final deliverable wasn't just a chart; it was a storytelling tool. I used specific visuals to highlight disparities without cluttering the screen.
Decomposition Tree
I used AI-powered Decomposition Trees to break down the "Gender Gap" by department. This instantly revealed that while HR and Marketing were balanced, Operations had a 90% male skew at the Director level.
Stacked Ribbon Chart
To visualize promotions over time, I used a Ribbon Chart. It clearly showed that while entry-level hiring was 50/50, the "promotion pipeline" narrowed significantly for women after the 3rd year of tenure.
5. Executive Impact
Data is useless unless it drives action. Based on this dashboard, the key takeaways presented to the engagement partners were:
🚨 The "Broken Rung" Discovery
The data proved that the issue wasn't hiring women; it was promoting them to Manager. The biggest drop-off happened between "Senior Associate" and "Manager." This insight shifted the strategy from "Recruitment Campaigns" to "Internal Mentorship Programs."
Final Thoughts
This project reinforced that a Digital Marketer/Analyst must be versatile. Whether it's Marketing ROI or HR Diversity, the skill set is the same: Clean Data + Smart Logic + Clear Storytelling = Better Decisions.
Need help visualizing complex data?
I specialize in turning messy Excel sheets into automated Power BI dashboards. Let's discuss how we can unlock insights for your business.
