9 Replies Latest reply on Sep 19, 2018 10:13 PM by Vijay Kulkarni

# Nett Expansion Rate View

Hi,

I am looking to create a graph that shows MOM nett expansion rate: ((Churn Revenue - Expansion Revenue)/Previous Month Revenue)*100

Churn Revenue: If a person spend less \$ in month than in (month - 1) then the difference in revenue is churned revenue. Eg: Mr A spend \$10 in July but \$8 in August then it comes under churn revenue bin and the churned revenue is \$2 for month of August.

Expansion Revenue: If a person spend more \$ in month than in (month - 1) then the difference in revenue is expansion revenue. Eg: Mr A spend \$10 in July and \$12 in August then the expansion revenue is \$2 for month of August.

Previous month Revenue: Revenue generated in last month (For August, last month revenue is of July and so on)

Note that a person who joined within 1 month period is New customer, hence he should not be included in the calculation. Eg: Mr A created his account in August 2018. Then in the August Nett expansion rate report he should not be considered.

I have attached a workbook for reference. Kindly note that all the customers are new on 01/01/2018. I am new to tableau and any help would be highly appreciated.

• ###### 1. Re: Nett Expansion Rate View

Hi Nandan,

Please find attached workbook. Is this what you are looking for?

Vijay

• ###### 2. Re: Nett Expansion Rate View

Hi Vijay,

Thanks for the response. I tried using it in my dashboard and I found that the function (eg. churned revenue) works in a way that it takes overall revenue of month and check with overall revenue of (month - 1). But what I need is each customer's individual revenue difference month on month and then added into either expansion or churn revenue depending upon the difference > 0 or not. Kindly use this image to get an understanding of my requirement. Please let me know if I am unable to comprehend my problem.

Regards,

Nandan

• ###### 3. Re: Nett Expansion Rate View

Nandan,

Here is revised workbook. Data in the shared workbook is different than what you show in picture.

If this does not work request you to share workbook with representative data.

Vijay

• ###### 4. Re: Nett Expansion Rate View

Hi Vijay,

I really appreciate for taking some time out of your busy schedule. Below is attached excel file and tableau file for your reference. In the excel file, the summary sheet will help you find what I actually need.

Kindly let me know if you need any other input from my side.

Regards,

Nandan

• ###### 5. Re: Nett Expansion Rate View

Nandan,

Here is workbook. Last sheet contains line chart what you want. Hope this works for you.

Vijay

• ###### 6. Re: Nett Expansion Rate View

Hi Vijay,

I think I am not able to clearly state the problem here.

What you are creating is: Overall revenue difference b/w consecutive months if > 0 then expansion and if < 0 then churn.

But what I am looking is for every customer there can be churn or expansion. These churn and expansion are summed up in their respective bins (churn or expansion) and then the difference of overall churn (coming from individuals whose revenue churned from previous month) and overall expansion (coming from individuals whose revenue expanded from previous month) divided by last month's overall revenue is taken as nett expansion rate.

You can see from your tableau report that for any particular month where expansion <> 0, churn = 0 and vice versa. But it should not be the case. Both churn and expansion revenue can (should) be non zero for any month. Kindly refer the 3rd table from summary sheet of the excel file shared by me. You can see churn and expansion revenue as non zero for every month.

Please let me know if you need any other information.

Regards,

Nandan

• ###### 7. Re: Nett Expansion Rate View

Nandan,

Here is updated file. Now I added table matching with your Excel table. Hope this is what you want.

Vijay

• ###### 8. Re: Nett Expansion Rate View

Hi Vijay,

Can you please help me with the first() function. How is that working here and what is the use of customer filed in row?

Regards,

Nandan

• ###### 9. Re: Nett Expansion Rate View

Nandan,

Here are steps -

Drag pills as shown -

Go to any measure value and edit table calculation and check how Nested calculation is done. This gives total Revenue, Churn Revenue, Expansion Revenue and Net Expansion Rate for each month appearing for every customer.

To keep one customer insert First() function on row shelf and make it Discrete and Compute using - Customer. Apply filter for First()=0

Here you will get help on function First() - Table Calculation Functions

Go to pills First() and Customer and deselect Show Header option to get -

Hope this helps.

Vijay