cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Sales Contribution by Customers Cohort Analysis

Dear, 

I want to build a bar chart that demonstrate Sales Contribution of Each Year by customers from each year. 

 For example: I want to see how many customers who made 1st purchase in 2012 or 2013 that contribution to the Total Sales in 2014

To clarify, I would like to put an image here as the output result. 

Sales Contribution.PNG

I am used Tableau to build this chart. but I am quite new to Power Bi, and really want to know how to build this chart by Power Bi. 

Dataset for this: sample_superstore

 

Hope you could help me!

Thanks so much!! 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
sreenathv Member
Member

Re: Sales Contribution by Customers Cohort Analysis

To your table name "Orders", add the following calculated column

 

 

CustomerFirstPurchaseYear = YEAR(
  MINX(
    FILTER(ALL(Orders),
    Orders[Customer ID]=EARLIER(Orders[Customer ID])
    )
  ,Orders[Order Date]
  )
)

You don't have a customer master, so you have to use this formula instead of the one I suggested first.

 

If you use this column on your Legend and OrderDate's year on your axis with "Sales" on the values, you will get the results on your stacked column chart.

 

View solution in original post

sreenathv Member
Member

Re: Sales Contribution by Customers Cohort Analysis

I have never created a Cohort Analysis and I don't actually understand what those percentages are or what are Q0 through Q15.

 

But out of curiosity, and based on your post, I assume you want to find the following

 

1) How many new customer IDs were created each quarter?
2) How many of them purchased in the subsequent quarters?
3) Show the ratio counts as a percentage.

For the sake of better understanding, I will illustrate the workings step by step so that you will get started on this and you will be able to modify the codes on your own to suit your requirements.


Add the following Columns to your "Orders" table.

 

Step1: Add a calculated column for Customer's First Purchase Date

CustomerFirstPurchaseDate =
MINX (
    FILTER ( ALL ( Orders ), Orders[Customer ID] = EARLIER ( Orders[Customer ID] ) ),
    Orders[Order Date]
)

Step2: Add a calculated column for Order's Quarter No

OrderQtrNo =
VAR BaseYear =
    YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) )
VAR OrderYear =
    YEAR ( Orders[Order Date] )
VAR MF = ( OrderYear - BaseYear ) * 4
RETURN
    Orders[Order Date].[QuarterNo] + MF

Step3: Add a calculated column for Customer's Q00

 

CustomerQ0 =
VAR BaseYear =
    YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) )
VAR CFPYear =
    YEAR ( Orders[CustomerFirstPurchaseDate] )
VAR MF = ( CFPYear - BaseYear ) * 4
RETURN
    Orders[CustomerFirstPurchaseDate].[QuarterNo] + MF

Step4: Add a calculated column for Customer's 1st Quarter as text ( like 2014-Q3 etc...)

 

FirstQuarterOfCustomer =
Orders[CustomerFirstPurchaseDate].[Year] & "-Q" & Orders[CustomerFirstPurchaseDate].[QuarterNo]

Step 5: Add a calculated column for Customer's Returning Quarters

 

ReturningQuarterNo =
"Q"
    & FORMAT ( Orders[OrderQtrNo] - Orders[CustomerQ0], "00" )

Step 6: Add a measure for finding the count of active customers.

 

ActiveCustomerCount = DISTINCTCOUNT(Orders[Customer ID])

Step 7: Add a measure to find the total number of customers activated each quarter.

TotalCustomers =
CALCULATE (
    DISTINCTCOUNT ( Orders[Customer ID] ),
    Orders[ReturningQuarterNo] = "Q00"
)

Step 8: Add a measure to find the percentages

CA = DIVIDE([ActiveCustomerCount],[TotalCustomers],0)

Here is the result...

CA.jpg

 

 

 

View solution in original post

7 REPLIES 7
Highlighted
sreenathv Member
Member

Re: Sales Contribution by Customers Cohort Analysis

If you have a relationship between CustomerMaster and the SalesTable, you can create the Cohort Analysis by adding a column to the CustomerMaster using the following function.

 

CustomerFirstBillingYear = 
YEAR( MINX(RELATEDTABLE(SalesTable), SalesTable[InvoiceDate]))

This column will bring the year of the earliest date(using MINX) to your customer master table.

 

Further, you can use a "Stacked Column Chart" and "100% Stacked Column Chart" visuals to do the Cohort analysis by adding the Actual Year of the transaction to the Axis and CustomerFirstBillingYear to the Legend of the charts.

 

Anonymous
Not applicable

Re: Sales Contribution by Customers Cohort Analysis

power bi sales contribution.PNG

1st purchase.PNG

Thanks for reply and solution- 

I tried you formula but it doesn't work or give the result that I expected. 

May I wrong at something. If yes, please help me to figure it out. 

 

Thanks and hope to hear from you!

I used data: sample_superstore

 

sreenathv Member
Member

Re: Sales Contribution by Customers Cohort Analysis

I have tried that in my PBI Desktop and it's working.

 

  1. I have a calendar table which is linked to the date field of the transactions. The transaction year is in this calendar table.
  2. I have a customer master which is linked to the customer id of the transactions.
  3. I have added a calculated column to find the firstbillingyear of the customer to the Customer Master table.

After doing so, I have tried using a Stacked Column Chart and it worked.

 

If it is not working, can you post some sample data of all your tables? At least the Order Table and the Customer Master. I will copy the data into my desktop and try it.

Anonymous
Not applicable

Re: Sales Contribution by Customers Cohort Analysis

Dear, 

Thank you reply and helping. 

https://community.tableau.com/docs/DOC-10198 

This is the dataset that I am using. 

Please take time to download and help me to solve the issue!!

Many thanks and appreciation!!

 

 

sreenathv Member
Member

Re: Sales Contribution by Customers Cohort Analysis

To your table name "Orders", add the following calculated column

 

 

CustomerFirstPurchaseYear = YEAR(
  MINX(
    FILTER(ALL(Orders),
    Orders[Customer ID]=EARLIER(Orders[Customer ID])
    )
  ,Orders[Order Date]
  )
)

You don't have a customer master, so you have to use this formula instead of the one I suggested first.

 

If you use this column on your Legend and OrderDate's year on your axis with "Sales" on the values, you will get the results on your stacked column chart.

 

View solution in original post

Anonymous
Not applicable

Re: Sales Contribution by Customers Cohort Analysis

Dear, 

it works perfectly. 

Thanks so much. 

 

I also posted how to build the Cohort Analysis  in this forum to know the return of customer through months.. If you have time, please help me also! 

 

here the picture I attached the final result. Cohort Analysis example.PNG

Thanks for spending time to help me!!!

sreenathv Member
Member

Re: Sales Contribution by Customers Cohort Analysis

I have never created a Cohort Analysis and I don't actually understand what those percentages are or what are Q0 through Q15.

 

But out of curiosity, and based on your post, I assume you want to find the following

 

1) How many new customer IDs were created each quarter?
2) How many of them purchased in the subsequent quarters?
3) Show the ratio counts as a percentage.

For the sake of better understanding, I will illustrate the workings step by step so that you will get started on this and you will be able to modify the codes on your own to suit your requirements.


Add the following Columns to your "Orders" table.

 

Step1: Add a calculated column for Customer's First Purchase Date

CustomerFirstPurchaseDate =
MINX (
    FILTER ( ALL ( Orders ), Orders[Customer ID] = EARLIER ( Orders[Customer ID] ) ),
    Orders[Order Date]
)

Step2: Add a calculated column for Order's Quarter No

OrderQtrNo =
VAR BaseYear =
    YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) )
VAR OrderYear =
    YEAR ( Orders[Order Date] )
VAR MF = ( OrderYear - BaseYear ) * 4
RETURN
    Orders[Order Date].[QuarterNo] + MF

Step3: Add a calculated column for Customer's Q00

 

CustomerQ0 =
VAR BaseYear =
    YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) )
VAR CFPYear =
    YEAR ( Orders[CustomerFirstPurchaseDate] )
VAR MF = ( CFPYear - BaseYear ) * 4
RETURN
    Orders[CustomerFirstPurchaseDate].[QuarterNo] + MF

Step4: Add a calculated column for Customer's 1st Quarter as text ( like 2014-Q3 etc...)

 

FirstQuarterOfCustomer =
Orders[CustomerFirstPurchaseDate].[Year] & "-Q" & Orders[CustomerFirstPurchaseDate].[QuarterNo]

Step 5: Add a calculated column for Customer's Returning Quarters

 

ReturningQuarterNo =
"Q"
    & FORMAT ( Orders[OrderQtrNo] - Orders[CustomerQ0], "00" )

Step 6: Add a measure for finding the count of active customers.

 

ActiveCustomerCount = DISTINCTCOUNT(Orders[Customer ID])

Step 7: Add a measure to find the total number of customers activated each quarter.

TotalCustomers =
CALCULATE (
    DISTINCTCOUNT ( Orders[Customer ID] ),
    Orders[ReturningQuarterNo] = "Q00"
)

Step 8: Add a measure to find the percentages

CA = DIVIDE([ActiveCustomerCount],[TotalCustomers],0)

Here is the result...

CA.jpg

 

 

 

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,785)