Forum Discussion
jaylyn
Helper I
Helper IStatic Measure-ALL() and Removefilters() not working
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 ra...
rajendraongole1
Super User
Super UserHi 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 .
jaylynHelper I
Helper IHi, 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:
Active-1ago =
CALCULATE(
DISTINCTCOUNT('Employee'[EED]),
Employee[Hiredate] <= EOMONTH(MAX('Date'[Date]), -1) &&
Employee[TerminationDate]> EOMONTH(MAX('Date'[Date]), -1)
)
----
Active-10ago =
CALCULATE(
DISTINCTCOUNT('Employee'[EEID]),
Employee[Hiredate] <= EOMONTH(MAX('Date'[Date]), -10) &&
Employee[Termination_Date]> EOMONTH(MAX('Date'[Date]), -10)
)
i have this measure 12x (including current-month) - each time i'm changing the -10 to -2 or -3 and etc.
avg headcount last_12months =
([Active] +
[Active-1ago] +
[Active-2ago] +
[Active-3ago] +
[Active-4ago] +
[Active-5ago] +
[Active-6ago] +
[Active-7ago] +
[Active-8ago] +
[Active-9ago]+
[Active-10ago] +
[Active-11ago]
)
/ 12
//calculates avg HC for last 12 months, including current month
here is my tenure bucket:
Tenure bucket =
SWITCH(
TRUE(),
Employee[TenureMonth] <= 3, " 0-3 Months",
Employee[TenureMonth] <= 6, " 3-6 Months",
Employee[TenureMonth] <= 9, " 6-9 Months",
Employee[TenureMonth] <= 12, " 9-12 Months",
Employee[TenureMonth] <= 18, "12-18 Months",
Employee[TenureMonth] <= 24, "18-24 Months",
Employee[TenureMonth] <= 30, "24-30 Months",
Employee[TenureMonth] <= 36, "30-36 Months",
Employee[TenureMonth] <= 42, "36-42 Months",
Employee[TenureMonth] < 48, "42-48 Months",
TRUE(), " 48+ Months" // Adjusted to three spaces before "48+ Months"
)