Calculating Churn Rate on Cohort analysis show-case

DAX DAX Calculations
1

I have 2 Questions:

Q-1
How can we create calculated column calculate the churn rate for each row of the cohort (Start from 1 to 12)?

Here is the equation I got it from one of the sites:
Churn rate=User Lost this year/(Users at Start of Year+Users Added This year)

Here’s an Annual Churn Rate Example:

Users at start of year (Cohort) : 50,501
New users added during year (Cohort): 16,765
Users lost at the end of year: 27,890
Annual churn rate: 27,890/67,266 = 41.5%

How can we implement it inside the show-case?

Q-2

Rentintion
Rentintion.JPG891×613 79.7 KB

The above picture display cohort information based on the number of unique customers on each period of a cohort, I need to display the cohort based on customer sales not on the number of the customers what sort of changes I should do?

Kindly show me this calculation in your latest Cohort analysis showcase introduced in 2018 (I think in last November 2018).

Thank You

2

I think all the ingredients are there to achieve this.

For sales amount you likely just need to change the key calc in the below forumla - ‘Unique Customers’ to what sales amount you want.

Retention Period = 
VAR CustomerDimension = VALUES( Customers[Customer Names] )

RETURN
IF( SELECTEDVALUE( 'Cohort Periods'[Period] ) = 0, [Unique Customers],
CALCULATE(
  CALCULATE( [Unique Customers],
        FILTER( CustomerDimension,
            COUNTROWS(
                FILTER( 'Cohort Periods',
                    [Retention Days] > 'Cohort Periods'[Min Days] && [Retention Days] <= 'Cohort Periods'[Max Days] ) ) > 0 ) ),
                          TREATAS( VALUES( Dates[Month & Year] ), Customers[Join Month Cohort] ) ))

Then re-arrange the below formula with the updated formula above and then a ‘total sales’

Retention % = 
CALCULATE(
    DIVIDE( [Retention Period], [Unique Customers], 0 ),
        TREATAS( VALUES( Dates[Month & Year] ), Customers[Join Month Cohort] ) )

Everything is there in the example file from this session below.

I recommend going through this in detail and really understanding the concepts explained during this one.

Everything is there around the techniques required to get the answer you require for this. It just requires inputting what you require into the correct formula.

3

Thank you so much Sam

4

Thank you Sam , Is this equation related to the ( Annual Churn Rate) equation I mentioned it in Q.1?
For Q.1 I mean the (No Of Customers) , For Q.2 I mean the Sales

5

Yes I believe they are exactly the same thing, from what I understand of your question. Exactly the same technique should get you the same thing.

6

Ok thank you , I’ll do practices on it and if I find some problems I’ll get back to you.

7

I think Sam You didn’t understand my Question clearly the (Retention %) , according to your solution it always display 100% which is not true , Please return back and read the question again , the resources is available on your website here is a link example if you want to know how to calculate the churn rate https://clevertap.com/blog/churn-rate/ .

8

November 2018 Learning Summit Demo.pbix (1.6 MB)

Please check the (CLTV) Measure is the calculation correct?

9

Hi @sam.mckay

Please how can I have access to this resources, it’s showing lock

10

Hi @Mubar

I did a search on the portal for Nov 2018 Learning Summit. Its no longer available on the site.

I also did a search on the portal and retrieve the following that might be able to help you.

image
image1158×745 50.4 KB

I’m not sure is @EnterpriseDNA or @sam.mckay can pull it up for you.

thanks
Keith

11

Yeah, I couldn’t find the search but I can’t access the portal resources.

Thanks for your help!
Mubar