Day 17: Power BI — HR Analytics Use Cases

Key measures: Total Employees = COUNTROWS(Employees). Active Employees = CALCULATE(COUNTROWS(Employees), Employees[Status] = "Active"). Leavers = CALCULATE(COUNTROWS(Employees), Employees[Status] = "Left"). Attrition Rate = DIVIDE([Leavers], ([Active Employees] + [Leavers]) / 2). Visuals: Card KPIs for Total, Active, Leavers, and Attrition Rate. Column chart for attrition by department. Line chart for monthly attrition trend. Bar chart for attrition by tenure band. Slicer for Year and Department.

Avg Salary = AVERAGE(Employees[Salary]) This automatically responds to Department slicers and filters. For salary band distribution: create a calculated column: Salary Band = IF([Salary] < 300000, "<3L", IF([Salary] < 600000, "3-6L", IF([Salary] < 1000000, "6-10L", ">10L"))). Then a column chart with Salary Band on axis and Count of Employees as values shows the distribution. Gender pay gap analysis: create two measures (Male Avg Salary and Female Avg Salary using CALCULATE with gender filter) and put them side by side in a clustered bar chart.

Headcount at a point in time requires knowing who was active on each date — more complex than simple aggregation because employees join and leave at different times. Approach: create a Date table. For each date, count employees where JoiningDate <= Date AND (LeavingDate > Date OR LeavingDate is blank). DAX: Headcount = CALCULATE(COUNTROWS(Employees), FILTER(Employees, Employees[JoiningDate] <= MAX('Date'[Date]) && (ISBLANK(Employees[LeavingDate]) || Employees[LeavingDate] > MAX('Date'[Date])))). Plot on a line chart with Date on axis.

Rating distribution is a simple column chart: Axis = Performance Rating (1-5), Values = Count of Employees. Ensure Rating is treated as a category (text or whole number for ordering). Expected normal distribution: few 1s, more 2s, most 3s, fewer 4s, few 5s. Comparison: use a clustered column chart with Rating on axis, Department on legend — shows rating distribution by department side by side. To identify bell curve vs skewed distribution, overlay a line chart of expected distribution using a static reference table.

Key recruitment metrics: Time to Hire = AVERAGE(DATEDIFF(Applications[PostingDate], Applications[JoiningDate], DAY)). Offer Acceptance Rate = DIVIDE(COUNTROWS(FILTER(Applications, [Status]="Accepted")), COUNTROWS(FILTER(Applications, [Status] IN {"Accepted","Declined"}))). Source of Hire distribution: donut chart with Hiring Source on values and Count on measures. Pipeline funnel: bar chart showing count at each stage (Applied → Screened → Interviewed → Offered → Joined) with conversion rates as data labels.