Help with Turnover Rate Calculation – Keeping Headcount Stable While Filtering

[snwileu7]

Hi Fabric Community,
I'm working on a turnover rate calculation in Power BI and running into a challenge with how filters affect my headcount denominator.
Here’s the setup:

Table A contains employee profile data (e.g., employment status, career stream, worker type).
Table B contains termination records (e.g., termination date, reason).
I calculate turnover rate as:
Turnover Rate = Terminations / Average Headcount

✅ What I Need:

The termination count should respond to filters (e.g., career stream, location, time).
The average headcount should:

Be calculated over completed months in a selected time period (monthly, quarterly, annually).
Respond to filters from Table A (e.g., career stream).
Ignore filters from Table B (to avoid inflating the turnover rate).

❌ What’s Going Wrong:

When I apply filters from Table B (like termination reason), the headcount shrinks.
When I try to remove filters using REMOVEFILTERS(Table B), the headcount becomes static and doesn’t respond to filters from Table A.

🔍 What I’ve Tried:

Using CALCULATE([Monthly Headcount], REMOVEFILTERS(Table B))
Wrapping AVERAGEX over filtered months
Creating disconnected tables for filtering

💡 What I’m Looking For:

Best practices for keeping headcount filter-responsive to employee attributes but immune to termination filters.
Whether disconnected tables or alternate modeling approaches are better.
Any DAX patterns or modeling tips that have worked for others.

Thanks in advance for your help!

[v-venuppu]

Hi @snwileu7 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @snwileu7 ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-venuppu]

Hi @snwileu7 ,

Thank you for reaching out to Microsoft Fabric Community.

You can fix this by making your headcount measure ignore filters from the Terminations table while still responding to Employee filters.

Average Headcount =
CALCULATE(
AVERAGEX(VALUES('Date'[MonthYear]), [Monthly Headcount]),
REMOVEFILTERS('Terminations'),
KEEPFILTERS(VALUES('Employees'))
)

This keeps the denominator stable while reflecting attributes like career stream or location.
Ensure relationships flow Date --> Employees --> Terminations (single direction).
Your turnover rate = Terminations / Average Headcount will then behave correctly across all filters.