Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ejafarov
Helper I
Helper I

Churn rate by tenure group and age group

Dear Power BI Community,

I hope this message finds you well. I am currently working on a project where I need to calculate the churn rate for combinations of tenure and age groups in Power BI. I have multiple measures that find employee count for active employees and employees 12 months before the selected date, for each combination of tenure groups and age groups.

My goal is to find the churn rate (turnover) for each combination of tenure and age groups for active employees and employees 12 months before the selected date.

Here are the measures I have:

  1. ActiveEmployeeCount:
  2. EmployeeCount12MonthsBefore
  3. Employee count  left during 12 months 

I need help creating a measure to calculate the churn rate and then display the results in a matrix visualization.

I have tried the following DAX formula to calculate the churn rate, but I would appreciate it if someone could confirm whether it's correct or suggest an alternative method:

4 REPLIES 4
v-tangjie-msft
Community Support
Community Support

Hi @ejafarov ,

 

Please try this:

Measure one Filtered =

CALCULATE (

[Cnt_Active_AgeGr],

FILTER (All('Join of age and tenure'), MAX('Join of age and tenure'[Tenure Bucket for active] )= MAX('Join of age and tenure'[Tenure Group]) &&

MAX('Join of age and tenure[Age Bucket for active]) =MAX('Join of age and tenure'[Age Group])

)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Dear,

 

Sorry for the unclear format. I am adding the sample table from my file.

Currently, I have measures to calculate the count of employees for age group and tenure group separately. However, I need a DAX measure that will consider the selected filter (e.g., 31.12.2022) and calculate the active employee count by a combination of age group and tenure group. Ultimately, I want to find the churn rate based on this data.

 

Could you please explain me steps or dax?

 

Thanks

 

IDStart DateResignation dateBirth Date
1925/02/2009 07/02/1985
3711/05/2012 24/12/1980
98001/03/2017 24/12/1992
167715/04/2022 18/06/1981
99020/12/2021 09/07/1987
125903/06/2019 09/02/1986
91231/10/2016 19/01/1988
121611/02/201928/12/202225/09/1994
117101/10/2018 11/04/1995
154010/06/2021 22/11/1979
125601/05/201914/01/202207/10/1986
124517/01/202212/05/202207/10/1986
166401/03/2022 26/01/1993
139506/03/2020 15/06/1977
105701/12/2017 09/05/1996
136210/01/2020 12/01/1990
108205/02/2018 06/06/1995
125220/05/201905/05/202223/08/1985
142201/07/2020 27/05/1988
177516/12/2022 05/01/1999
148805/01/2021 29/01/1994
140113/03/2020 02/06/1966
8501/02/2013 08/12/1990
67114/09/2016 29/03/1982
109202/04/2018 26/05/1995
156001/07/2021 27/09/1999
177919/12/2022 20/03/2001
29024/08/2015 06/11/1991
163409/12/202109/03/202211/01/1997
169525/05/202209/09/202214/04/2001
147901/12/2020 30/01/1991
153401/06/202106/06/202208/12/1982
135808/01/2020 23/04/1993

Hi @ejafarov ,

 

Do you mean you want the measure results to be affected by the slicer?

You can use ALLSELECTED () function.

Please refer to the following documents for more information.

Introducing ALLSELECTED in DAX - SQLBI

Calculating Employee Attrition Rate with DAX – Part 1 (mssqltips.com)

Customer Churn Rate - Microsoft Power BI Community

Churn Rate Measure - Microsoft Power BI Community

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

 

ejafarov
Helper I
Helper I

Measure one Filtered =

CALCULATE (

[Cnt_Active_AgeGr],

FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),

FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])

)

Measure 2 Filtered =

CALCULATE (

[Cnt_LY_AgeGr],

FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),

FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])

)

Measure 3 Filtered =

CALCULATE (

[Cnt_Active_TenGr],

FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),

FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])

)

Measure 4 Filtered =

CALCULATE (

[Cnt_LY_TenureGr],

FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),

FILTER ('Join of age and tenure', [Age Bucket for active] = 'Join of age and tenure'[Age Group]

Measure one Filtered =

CALCULATE (

[Cnt_Active_AgeGr],

FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),

FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])

)

Measure 2 Filtered =

CALCULATE (

[Cnt_LY_AgeGr],

FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),

FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])

)

Measure 3 Filtered =

CALCULATE (

[Cnt_Active_TenGr],

FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),

FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])

)

Measure 4 Filtered =

CALCULATE (

[Cnt_LY_TenureGr],

FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),

FILTER ('Join of age and tenure', [Age Bucket for active] = 'Join of age and tenure'[Age Group])

)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors