Forum Discussion

PowerBIKD's avatar
PowerBIKD
Regular Visitor
4 years ago
Solved

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

 

average
Cohort Analysis
DAX
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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

    • PowerBIKD's avatar
      PowerBIKD
      Regular Visitor
      4 years ago

      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

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.