Cohort Analysis: Average Churn Rate

[PowerBIKD]

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

[v-kkf-msft · Accepted solution]

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.

[amitchandak]

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

[PowerBIKD]

Hi @amitchandak 

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