Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
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:
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
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 |
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.
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])
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
14 | |
12 | |
12 | |
9 |
User | Count |
---|---|
63 | |
22 | |
21 | |
17 | |
11 |