User Question on our Youtube channel: I have a question on attrition rate – for e.g. we have employees who quit voluntarily and involuntarily. there were 500 employees who quit in 2024, and number of employees were 5000. In this case the total attrition rate is 10%. However, only 400 employees quit voluntarily. Now technically the attrition % should be 400/5000 but the moment you select voluntarily as reason, the denominator of 5000 also changes which is incorrect. what do you suggest here?

Answer / Solution

---

🔍 Problem Recap:

• Total Employees (2024): 5000
• Total Attrition (2024): 500 → Attrition Rate = 500 / 5000 = 10%
• Voluntary Attrition: 400 → You want Voluntary Attrition Rate = 400 / 5000 = 8%

But if you use a slicer on “Reason = Voluntary”, your dataset shrinks:

• Numerator becomes: 400 ✅
• Denominator becomes: 400 ❌ (filtered to only voluntary exits)

So Power BI wrongly shows 400 / 400 = 100%

---

✅ Solution:

You need to decouple the denominator from the slicer filter so that it always considers the full population of employees (or total attrition), not just filtered voluntary ones.

---

🧠 Suggested DAX Measure:

Voluntary Attrition Rate = 
DIVIDE(
    CALCULATE(COUNTROWS('AttritionData'), 'AttritionData'[Reason] = "Voluntary"),
    CALCULATE(COUNTROWS('Employees'), ALL('AttritionData'[Reason]))
)

Explanation:

• COUNTROWS('AttritionData') with filter "Voluntary" → numerator = 400
• ALL('AttritionData'[Reason]) → removes the slicer filter on Reason for the denominator

---

🛠️ Alternate approach (for more complex models):

If Employees is a separate table, ensure you’re using the total headcount from that table, not from filtered attrition table.

Voluntary Attrition Rate = 
DIVIDE(
    CALCULATE(COUNTROWS('AttritionData'), 'AttritionData'[Reason] = "Voluntary"),
    CALCULATE(COUNTROWS('Employees'), ALL('AttritionData'))
)

Or even more precise, using distinct employee IDs:

Voluntary Attrition Rate = 
DIVIDE(
    CALCULATE(DISTINCTCOUNT('AttritionData'[EmployeeID]), 'AttritionData'[Reason] = "Voluntary"),
    CALCULATE(DISTINCTCOUNT('Employees'[EmployeeID]), ALL('AttritionData'))
)

---

✅ Best Practice:

Use the ALL() function on the column/table that is being filtered by the slicer to override slicer behavior for the denominator.

---