Welcome to our comprehensive guide on how to create an HR Attrition and Head Count Analysis Dashboard in Excel, complete with a free downloadable template! This user-friendly dashboard, built using Power Pivot and DAX measures, is an essential tool for HR professionals looking to analyze and manage workforce data effectively.
Understanding the Dashboard
Dashboard Sheet Tab: A Visual Overview
- Interactive Slicers: Filter data based on Phase, Process, Sub Process, Year, Month, Gender, and Tenure.
- Head Count Card: At the top section, view the total head count.
- Attrition Analysis: Visualize Total Attrition%, YTD Attrition%, and Annualized Attrition% using semi-circle charts with data labels.
- Attrition Breakdown: Explore Involuntary and Voluntary Counts on a doughnut chart, with Total attrition number displayed in the center.
- Gender-Based Analysis: A column chart showcasing Attrition by Tenure (in months), featuring a Gender level drill-down option.
- Monthly Trends: The combo chart displays Voluntary, In-Voluntary, and Total Attrition by Month.
Table View Sheet Tab: In-Depth Data Analysis
- Slicers for Detailed Filtering: Similar to the Dashboard tab, allowing for precise data segmentation.
- Pivot Table Insights: Displays KPIs such as Head Count, Involuntary/Voluntary Counts, various Attrition Percentages (Voluntary, Involuntary, Total, YTD, Annualized).
Data Sheet Tab: The Backbone of Your Dashboard
Structured Data: Organized Excel table format, ready to be integrated into the data model for Power Pivot use.
Pivot Sheet Tab: Behind-the-Scenes Magic
Supportive Role: Although hidden, this tab contains multiple pivot tables that feed into the charts on the dashboard sheet.
Key Performance Indicators (KPIs)
To understand the dynamics of your workforce better, we’ve incorporated crucial KPIs:
- Total Headcount: Average headcount used for Attrition calculations.
- Involuntary Count: Number of involuntary separations.
- Voluntary Count: Number of voluntary separations.
- Attrition Percentages: Detailed percentages for Involuntary, Voluntary, and Total Attrition.
- YTD Attrition %: Year-to-date Attrition calculations.
- Annualized Attrition %: An estimated annual attrition rate, calculated from YTD Attrition %.
Harnessing the Power of DAX
In this dashboard, DAX functions play a vital role in data analysis and visualization. Below is the code of DAX measures used in the dashboard.
Total Head Count =AVERAGEX( SUMMARIZE(Data,Data[Month],"HC",CALCULATE(COUNTROWS(Data),Data[RsnCategory]="-")) ,[HC]) ============================================================================================== Monthly Head Count =VAR CM = MAX ( Data[Month] ) VAR PM = EDATE ( CM, -1 ) VAR CM_HC = CALCULATE ( [Total Head Count], Data[Month] = CM,ALL(Data[Month Name],Data[Year]) ) VAR PM_HC = CALCULATE ( [Total Head Count], Data[Month] = PM,ALL(Data[Month Name],Data[Year]) ) RETURN IF ( HASONEVALUE ( Data[Month] ), DIVIDE ( CM_HC + PM_HC, 2, BLANK () ), [Total Head Count] ) ======================================================================================== Involuntary Count =CALCULATE(COUNTROWS(Data),Data[RsnCategory] = "Involuntary") ======================================================================================== Voluntary count = CALCULATE(COUNTROWS(Data),Data[RsnCategory]= "Voluntary") ========================================================================================= Voluntary Attrition % =DIVIDE([Voluntary Count],[Monthly Head Count],BLANK()) ========================================================================================== In-Voluntary attrition % =DIVIDE([Involuntary Count],[Monthly Head Count],BLANK()) ========================================================================================== Total Attrition =DIVIDE([Involuntary Count]+[Voluntary Count],[Monthly Head Count],BLANK()) ========================================================================================== YTD Attrition =VAR YTD_Att = CALCULATE ( [Involuntary Count] + [Voluntary Count], ALL ( Data[Month Name], Data[Year] ), DATESYTD ( Data[Month], "3-31" ),ALL(Data[Month Name],Data[Year]) ) VAR YTD_HC = CALCULATE ( [Total Head Count], ALL ( Data[Month Name], Data[Year] ), DATESYTD ( Data[Month], "3-31" ),ALL(Data[Month Name],Data[Year]) ) RETURN DIVIDE ( YTD_Att, YTD_HC, BLANK () ) =========================================================================================== Annualized Attrition % = VAR Month_Count = CALCULATE ( DISTINCTCOUNT ( Data[Month] ), DATESYTD ( Data[Month], "3-31" ),ALL(Data[Month Name],Data[Year]) ) RETURN [YTD Attrition] * DIVIDE ( 12, Month_Count, BLANK () )
Wrapping Up
Creating an HR Attrition and Head Count Analysis Dashboard in Excel is not just about tracking numbers; it’s about gaining insights that drive strategic decisions. With our easy-to-follow guide and free downloadable template, you’re well on your way to mastering workforce analytics. Happy analyzing!
Visit our YouTube channel to learn step-by-step video tutorials
