HR Analytics & Pay Equity Report

My role

BI Consultant & Data Analyst — end-to-end from HR system data to a live, self-service dashboard

The challenge

An organization had no consolidated way to monitor workforce composition, retention or pay equity across departments and business units. HR could see headcount changes reactively but had no structured view of turnover trends, no visibility into whether compensation was equitable across gender, age or ethnicity, and no way for department heads to self-serve basic workforce questions without a manual pull from HR.

The data problem

Core employee data lived in the organization's HR system, but individual departments maintained supplementary records separately in Excel and CSV files — headcount notes, role classifications, local tracking that never made it back into the central system. No single source could answer department-level questions accurately, there was no consistent tenure or bonus-category classification, and salary data had never been broken down in a way that could surface pay gaps.

My approach

I built a Data Factory pipeline in Microsoft Fabric pulling core employee data from the HR system while also ingesting the department-maintained Excel and CSV files, landing everything in a Fabric Lakehouse as a unified staging layer rather than treating the department files as one-off manual additions. From there I used Dataflows Gen2 and SQL in the Fabric Warehouse to reconcile the two: matching employee records across sources, resolving inconsistent job title and department naming, and building standardized classification fields for tenure group, age group and bonus category that didn't exist in the source data.

With the model unified, Power BI connected to the Warehouse and I built two views around how HR actually operates: an Employees page tracking headcount movement, hires, separations, retention and turnover with drill-downs by ethnicity, age group, tenure, department and job title plus a global map of workforce distribution, and a Finance page built around compensation equity — average salary by ethnicity, department, age group and tenure group, each split by gender so pay differences are visible immediately rather than buried in an aggregate.

The most technically involved piece was the gender pay gap trend visual: a year-by-year comparative view built with DAX measures calculating average male versus female salary across two decades of hire data, letting HR see not just the current gap but whether it has been narrowing or widening. I also built a job-title-level salary comparison table with conditional formatting flagging which gender earned more in each role, since aggregate department averages can hide role-level disparities.

The result

The dashboard gave HR and leadership a live, self-service view of workforce health for the first time. It surfaced an 18.58% turnover rate against 81.42% retention, giving a clear baseline to track against. The pay equity analysis revealed male employees held a higher average salary overall ($114K versus $112K), with the gap reversing in specific roles such as Sr. Manager and Controls Engineer where female average pay was actually higher — a level of detail completely invisible in a single company-wide average.

Tools & tech stack

• Microsoft Fabric Data FactoryPipeline pulling employee data from the core HR system alongside department Excel/CSV records
• Fabric LakehouseStaging layer unifying HR system data with department-level files
• Dataflows Gen2 & T-SQLReconciling records across sources, building standardized tenure, age and bonus classifications
• Power BI & DAXRetention/turnover calculations, multi-year gender pay gap trend, role-level salary comparison
• Conditional formattingJob-title salary tables flagging gender pay differences by role
• Geospatial visualizationMap view for global workforce distribution by country