Forum Discussion

snwileu7's avatar
snwileu7
Regular Visitor
1 year ago
Solved

Help with Turnover Rate Calculation – Keeping Headcount Stable While Filtering

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 employ...
Need Help
Tips and Tricks
  • v-venuppu's avatar
    v-venuppu
    1 year ago

    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.

v-venuppu's avatar
v-venuppu
Icon for Community Support rankCommunity Support
1 year ago

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.