Forum Discussion
Cohort Analysis tutorial
Dear,
I am new to Power BI.
Could you please to provide me the tutorial of how to build Cohort Analsysis in Power BI?
I used to make CA in Tableau, and now my job requires me to build CA in Power BI.
I searched on google how to build CA but not many sources taught CA in Power BI, there is 1 source but I have to pay fees. :(
I hope you could help me
Thanks so much!!
14 Replies
Hi ThoaNguyen,
According to your description, it seems that you want to get Cohort Analysis tutorial, right?
If possible, could you please inform me more detailed information(such as your data sample and your expecting output)? Then I will help you more correctly.
In addition, there are some posts for Cohort Analysis, you could try to refer to Cohort Analysis - DAX and Customer cohort analysis for details.
Best Regards,
Zoe Zhi
Dear,
Thank you for your reply.
I want my result look like this in the picture attached.
I want to see the percentage of customer (%) and the number of customers who re-purchase after their first purchase.
I use the sample - superstore dataset(xls). I can't find the attach button here so I send you a link the has the dataset.
Please download it and help me to build Cohort Analysis in Power Bi.
Thanks for spending time to help me!
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] + MFStep3: 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] + MFStep4: 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...
Guys, i need to do a cohort analysis to count the total number of hits on my software. Since each row belongs to my column it is an access. Can someone help me?
