cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Community Support

Hi @ejafarov ,

``````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.

Helper I

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

 ID Start Date Resignation date Birth Date 19 25/02/2009 07/02/1985 37 11/05/2012 24/12/1980 980 01/03/2017 24/12/1992 1677 15/04/2022 18/06/1981 990 20/12/2021 09/07/1987 1259 03/06/2019 09/02/1986 912 31/10/2016 19/01/1988 1216 11/02/2019 28/12/2022 25/09/1994 1171 01/10/2018 11/04/1995 1540 10/06/2021 22/11/1979 1256 01/05/2019 14/01/2022 07/10/1986 1245 17/01/2022 12/05/2022 07/10/1986 1664 01/03/2022 26/01/1993 1395 06/03/2020 15/06/1977 1057 01/12/2017 09/05/1996 1362 10/01/2020 12/01/1990 1082 05/02/2018 06/06/1995 1252 20/05/2019 05/05/2022 23/08/1985 1422 01/07/2020 27/05/1988 1775 16/12/2022 05/01/1999 1488 05/01/2021 29/01/1994 1401 13/03/2020 02/06/1966 85 01/02/2013 08/12/1990 671 14/09/2016 29/03/1982 1092 02/04/2018 26/05/1995 1560 01/07/2021 27/09/1999 1779 19/12/2022 20/03/2001 290 24/08/2015 06/11/1991 1634 09/12/2021 09/03/2022 11/01/1997 1695 25/05/2022 09/09/2022 14/04/2001 1479 01/12/2020 30/01/1991 1534 01/06/2021 06/06/2022 08/12/1982 1358 08/01/2020 23/04/1993
Community Support

Hi @ejafarov ,

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

You can use ALLSELECTED () function.

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.

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])

)

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors