cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

## Cohort Analysis: Average Churn Rate

Hello,
I'm new to Power BI and got struck while performing cohort analysis on customer data and trying to calculate the average across same row from multiple columns. I couldn't find any solutions related to it. So to start, I have created a dummy table with generate series to use it in cohort analysis and finding customer's that are retained. Upon doing that I have following as an example:

Customers that are joined in Jan 2021 have retention rate of 96.4% for a month after. I have calculated month over month churn rate. My goal is to get the average churn rate for customers joined in January 2021 based on their churn rates in months after. For example: Average churn rate for customers joined in Jan 2021 = 0.46 + 15.53 + 16.48 / 3 = 10.82 per month.

In this way I can use it to calculate the lifetime of a customer based on the average churn rate for customers that have their first sales on specific month. Hope this is clear.

Could someone please guide them how to proceed? Thanks a lot in advance.

Kind regards,

Karthik

1 ACCEPTED SOLUTION
Community Support

Hi @PowerBIKD ,

Please try the following formula:

``````Measure =
IF (
ISFILTERED ( 'Table'[Month After] ),
[CustMoMChurn%],
AVERAGEX (
FILTER (
SUMMARIZE (
'Table',
'Table'[Date],
'Table'[Month After],
"Cust", [CustMoMChurn%]
),
[Cust] > 0
),
[Cust]
)
)``````

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @PowerBIKD ,

Please try the following formula:

``````Measure =
IF (
ISFILTERED ( 'Table'[Month After] ),
[CustMoMChurn%],
AVERAGEX (
FILTER (
SUMMARIZE (
'Table',
'Table'[Date],
'Table'[Month After],
"Cust", [CustMoMChurn%]
),
[Cust] > 0
),
[Cust]
)
)``````

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@PowerBIKD , check if my blog can help

Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Regular Visitor

thank you for your reply and the blog link. I have done same analysis and got the customer retention.

My aim is now to calculate the average churn rate at which the customers are getting lost.

Below is the customer retention final result:

Customers that are joined in Jan 2021 have retention rate of 96.4% a month after. I have calculated month over month churn rate. I want the average churn rate for customers joined in January 2021 based on their churn rates in months after. For example: Average churn rate for customers joined in Jan 2021 should be = 0.46 + 15.53 + 16.48 / 3 = 10.82 per month. Not sure how I can calculate this using DAX?

Hope this is sufficient.

FYI: My data looks exactly like in your cohort analysis data if this helps.

Thanks a lot.

Kind regards,

Karthik

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors