This is the second part of a 3 parts project, you can find the full project here.
In the first part of this series, we built a robust Power BI data model to analyze employee attrition and satisfaction trends. Now, we’ll develop new measures and columns using DAX, a powerful formula language for data modeling and analysis in Power BI. This guide explains what DAX is, the difference between measures and calculated columns, and how to implement key DAX formulas for HR analytics. We’ll explore metrics for gender diversity, employee satisfaction, attrition, and more, providing actionable insights for decision-making.
In this article, we’ll focus on:
- Understanding DAX and its application in HR analytics.
- Differentiating between measures and calculated columns.
- Writing and implementing DAX formulas for key HR metrics such as attrition rates, satisfaction scores, and gender diversity.
The next steps in this series will cover:
- Part 3: Creating interactive and visually compelling dashboards in Power BI to visualize these insights and drive decision-making.
If you want to have access to the full Power BI file (with model, DAX and visualizations), contact me here.
Contents
What is DAX?
Data Analysis Expressions (DAX) is a formula language used in Power BI, Power Pivot, and Analysis Services to perform data calculations and aggregations. It is similar to Excel formulas but optimized for working with relational data models.
Key features of DAX include dynamic calculations that respond to filters and slicers in reports, a wide range of functions for filtering, aggregating, and time intelligence, and seamless integration with star schemas and large datasets.
To understand more about DAX you can visit the official Microsoft guide here: Data Analysis Expressions (DAX) Reference.
Measures vs. Calculated Columns
Understanding the difference between measures and calculated columns is crucial for creating an efficient Power BI model.
Measures are dynamic calculations evaluated based on the report context, like filters and slicers. They are used for aggregations like averages, sums, percentages, and ratios. Measures are lightweight and efficient because they are not stored in the data model but are calculated every time. For example, calculating the average salary across departments is best done using a measure.
Calculated columns are row-by-row calculations stored as part of the dataset. They are useful for static categorizations or calculations needed for grouping and filtering, such as age brackets or salary ranges. However, calculated columns take up storage space and can impact performance for large datasets.
Functions Covered in This Article
We’ll use the following DAX functions throughout this guide:
- COUNTROWS to count rows in a table
- CALCULATE to modify the context of a calculation
- DIVIDE to perform safe division avoiding errors from zero denominators
- AVERAGE to calculate the mean of a column
- SWITCH to implement conditional logic
- SUMX to iterate over a table for row-level calculations
General Metrics
The first step is to create foundational metrics like total employee count and average salary. These metrics provide the basis for further analysis.
To calculate the total number of employees, use the function COUNTROWS on the Employee table:
Employee Count = COUNTROWS(‘Fact’)
This measure provides the total headcount in the dataset and is a fundamental metric used in other calculations like attrition and gender diversity.
For average salary, use the AVERAGE function to calculate the arithmetic mean of the MonthlyIncome column:
AVG Salary = AVERAGE(‘Fact'[MonthlyIncome])
This measure gives a quick view of employee compensation trends and can be segmented by department or job role for more detailed analysis.
To calculate the average tenure (the period of time when someone holds a job) in the company, use again the AVERAGE function to calculate the mean of the YearsAtCompany column:
AVG Tenure = AVERAGE(‘Fact'[YearsAtCompany])
Gender Metrics
Analyzing gender diversity is essential for understanding workforce composition. We’ll calculate male and female percentages, gender disparity, and a balance score.
To calculate the percentage of male employees, use CALCULATE to filter rows where gender is “Male” and divide it by the total employee count using DIVIDE:
Male Percentage =
DIVIDE(CALCULATE(COUNTROWS(Demographics), ‘Demographics'[Gender] = “Male”), COUNTROWS(Demographics))
Similarly, for female employees, replace “Male” with “Female” in the above formula:
Female Percentage =
DIVIDE(CALCULATE(COUNTROWS(Demographics), ‘Demographics'[Gender] = “Female”), COUNTROWS(Demographics))
For the gender disparity ratio, divide the count of male employees by the count of female employees
Gender Disparity Ratio =
DIVIDE(
CALCULATE([Employee Count], Demographics[Gender] = “Male”),
CALCULATE([Employee Count], Demographics[Gender] =”Female”))
A ratio of 1 indicates perfect balance, while a value greater than 1 suggests more males than females.
To calculate the Gender Balance Score, subtract the female percentage from the male percentage, take the absolute value, and subtract it from 1:
Gender Balance Score =
1- ABS([Male Percentage] – [Female Percentage])
A score of 1 represents perfect balance, while a score closer to 0 indicates greater imbalance.
Attrition and Retention Metrics
Tracking attrition and retention rates is vital for workforce stability analysis.
To calculate the attrition rate, use CALCULATE to filter rows where attrition is “Yes” and divide by the total employee count:
Attrition Rate =
DIVIDE(
CALCULATE([Employee Count], ‘Fact'[Attrition] = “Yes”), [Employee Count])
Retention rate is the complement of attrition rate and can be calculated as:
Retention Rate = 1 – [Attrition Rate]
Satisfaction Metrics
Employee satisfaction is a critical measure for engagement and retention.
Create a calculated column for the Employee Satisfaction Score by averaging the three satisfaction columns (JobSatisfaction, EnvironmentSatisfaction, and RelationshipSatisfaction):
Employee Satisfaction Score =
(‘Satisfaction'[JobSatisfaction] +
‘Satisfaction'[EnvironmentSatisfaction] +
‘Satisfaction'[RelationshipSatisfaction])
/ 3
This calculated column allows you to analyze satisfaction at the employee level and aggregate it for departments or roles.
Advanced Metrics
To measure promotion rate, calculate the percentage of employees promoted within the past year. Use CALCULATE to filter rows where YearsSinceLastPromotion is 0 and divide by the total employee count:
Promotion Rate =
DIVIDE(
CALCULATE([Employee Count], ‘Fact'[YearsSinceLastPromotion] = 0),
[Employee Count])
To estimate the financial impact of attrition, use SUMX to iterate over employees who have left and multiply their annual income by a factor:
Attrition Revenue Impact = SUMX(
FILTER(‘Fact’, ‘Fact'[Attrition] = “Yes”),
‘Fact'[MonthlyIncome]*12*1.2)
Employee tenure is a key indicator of workforce stability. Instead of showing an average like “7.083 years,” it’s more intuitive to display it as “7 years and 1 month.” This format is clearer and easier to interpret for stakeholders.
Using DAX in Power BI, we calculate tenure by:
- Calculating the Average: Using AVERAGE(‘Fact'[YearsAtCompany]), calculated before in the first section.
- Extracting Years and Months: Splitting the result into whole years (INT) and converting the decimal into months (* 12).
- Conditional Check: If RemainingMonths = 0, exclude “0 months” and display only the years.
Average Tenure =
VAR AvgYears = [AVG Tenure]
VAR Years = INT(AvgYears)
VAR RemainingMonths = ROUND((AvgYears – Years) * 12, 0)
RETURN
IF(RemainingMonths = 0, FORMAT(Years, “0”) & ” years”,
FORMAT(Years, “0”) & ” years and ” & FORMAT(RemainingMonths, “0”) & ” months”)
Distribution Metrics
Categorizing employees by age and salary helps in segmentation.
Create a column using SWITCH to group employees into age brackets:
Age Bracket = SWITCH(TRUE(),
Demographics[Age] < 25, “Under 25”,
Demographics[Age] <=35, “25-35”,
Demographics[Age] <=50, “36-50”,
“50+”)
Similarly, group employees into salary ranges:
Salary Distribution = SWITCH(TRUE(),
‘Fact'[MonthlyIncome] < 3000, “Below 3000”,
‘Fact'[MonthlyIncome] <= 6000, “3000-6000”,
‘Fact'[MonthlyIncome] <= 10000, “6000-10000”,
“Above 10000”)
Conclusion
This article demonstrates how to use DAX in Power BI to transform raw HR data into meaningful metrics that provide actionable insights. By applying DAX functions such as COUNTROWS, CALCULATE, DIVIDE, AVERAGE, SWITCH, and SUMX, we calculated essential metrics like attrition rate, retention rate, employee satisfaction scores, and gender diversity metrics. These calculations help businesses better understand their workforce, identify gaps, and support data-driven decisions.
These metrics not only give a clear picture of current workforce trends but also allow us to explore relationships between different variables, like how compensation affects satisfaction or how promotions vary by department. For example, analyzing attrition with satisfaction metrics provides actionable insights into why employees leave and what improvements could retain talent.
We also discussed the importance of differentiating between measures and calculated columns. Measures are ideal for dynamic and context-aware calculations, while calculated columns are better suited for static categorizations or grouping data. This distinction ensures your model is both efficient and easy to maintain.
In Part 3 of this series, we will focus on creating an interactive dashboard in Power BI. This dashboard will bring the insights to life, allowing stakeholders to explore the data dynamically and identify actionable takeaways faster. Visualizing key metrics like gender balance, satisfaction trends, and attrition rates in an intuitive format will make it easier for decision-makers to interpret the data and act on it effectively.
Additionally, the techniques covered here are not limited to HR analytics. They can be adapted for any domain where DAX and Power BI are applicable.
If you want to have access to the full Power BI file (with model, DAX and visualizations), contact me here.
