I am working with Power BI and facing an issue with calculating turnover rates based on tenure buckets while keeping the average headcount for the last 12 months consistent across different tenure ranges. Specifically, the turnover rate should always use a static average headcount value, but the current setup causes the average headcount to vary based on the tenure bucket slicer applied.
Turnover Rate = terms_last_12months / avg headcount last_12months
Issue: When I place the avg headcount last_12months measure on a table with tenure buckets and terminations by tenure bucket, the average headcount value is being broken down by the tenure bucket slicer, rather than remaining static.
Desired Outcome: I want the avg headcount last_12months to remain static and consistent across all tenure buckets, regardless of the tenure bucket filter applied. This static average headcount should be used as the denominator in the turnover rate calculation, while the terminations should be accurately calculated based on the tenure bucket.
- Consistency Check: If the turnover rate shows, for example, 50.1% on a card, the sum of the turnover rates when broken down by tenure range should also total 50.1%. The calculated turnover rates by tenure bucket should match the overall turnover rate displayed on the card.
Attempted Solutions
- Using REMOVEFILTERS: Attempted to keep the average headcount static by removing filters on tenure buckets and dates, but this did not work as expected.
- Using ALL and ALLSELECTED Functions: Tried various combinations of these functions to achieve a static average headcount, but encountered issues with maintaining the static nature across different contexts.
- Creating a Static Table: Tried using a static table with a hardcoded value, but this is not ideal and does not update dynamically with the data.
I believe the best way to solve my situation is to make "avg headcount last_12months" static - please assist with this or show me another approach.
* disconnected date table (no relationship in data model with date and Employee table)
* Terms last 12M shows correctly when placed on table with tenure bucket
* turnover shows correctly when placed on table/card without the tenure bucket- when tenure bucket is added, it is spliced incorrectly
* In this example, 13.2% is what i should get
---------------------------
terms_last_12months (new) =
VAR currentdate = MAX('Date'[Date])
VAR end_of_current_month = EOMONTH(currentdate, 0)
VAR tweleve_month_ago_start = EOMONTH(end_of_current_month, -12) + 1
RETURN
CALCULATE(
DISTINCTCOUNT(Employee[EEID]),
Employee[TerminationDate] >= tweleve_month_ago_start &&
Employee[TerminationDate] <= end_of_current_month
)
0-3Months
terms last 12M = 4
avg hc last 12 months = 470
turnover = 4/470 = 0.851%
3-6Months
terms last 12M = 2
avg hc last 12 months = 470
turnover = 2/470 = 0.4255%
6-9Months
terms last 12M = 7
avg hc last 12 months = 470
turnover = 7/470 = 1.489%
9-12Months
terms last 12M = 5
avg hc last 12 months = 470
turnover = 5/470 =1.0638%
12-18Months
terms last 12M = 8
avg hc last 12 months = 470
turnover = 8/470 =1.702%
18-24Months
terms last 12M = 9
avg hc last 12 months = 470
turnover = 9/470 = 1.915%
24-30Months
terms last 12M = 7
avg hc last 12 months = 470
turnover = 7/470 = 1.489%
30-36Months
terms last 12M = 4
avg hc last 12 months = 470
turnover = 4/470 = 0.851%
36-42Months
terms last 12M = 1
avg hc last 12 months = 470
turnover = 1/470 = 0.213%
48+Months
terms last 12 months = 15
avg hc last 12 months = 470
turnover = 15/470 = 3.19%
avg hc last 12M = 470
term last 12 M = 62
turnover = 62/470 =13.2%
turnover % = 13.2%
Hi @jaylyn - create a measure that calculates the average headcount over the last 12 months without being affected by the tenure bucket slicer.
Static Avg Headcount Last 12 Months =
CALCULATE(
[Avg Headcount Last 12 Months],
REMOVEFILTERS('Tenure Table'[Tenure Bucket])
)
use the static average headcount measure in your turnover rate calculation measure
Turnover Rate =
DIVIDE(
[Terms Last 12 Months],
[Static Avg Headcount Last 12 Months],
0
)
Hope this works, still issue exist, please share the sample data for reference .
Proud to be a Super User! |  |
Hi, Raj-
i did do that but it's still behaving the same, it really doesn't make sense that the removefilters isn't working. Here is a calculation of my avg hc last 12M just to make sure it's not the problem:
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
