I'm trying to do a Cohort analysis of my customers in matrix. I have made a distinct count of new csutomers in a giving month (month 0) and added the count of distinct purchases in the following months (month 1-18). See image.
My question: How do I calculate and visulize these numbers as percentage of the first column (month 0 / new customers in the given period)?
The result should be something like:
Please advise a solution :-)
Solved! Go to Solution.
Here is a picture of my sample data. Hope it make sense.
DateOfFirstPurchase is calculated in the folowing way:
DateOfFirstPurchase = calculate(FIRSTDATE(Query1[Posting Date]); ALLEXCEPT(Query1; Query1[Sell-to Customer No_]))
MonthsSinceFirstPurchase = DATEDIFF(Query1[DateOfFirstPurchase]; Query1[Posting Date]; MONTH)
You should share the formula used in the matrix of previous post. You use the Year as row, month as column level, what's your values level?
I'm using the following formula to count the number of customers. The formula is inserted as values in the matrix.
NoOfCustomers = DISTINCTCOUNT(Query1[Sell-to Customer No_])
You can use the following measure:
Measure = DIVIDE(DISTINCTCOUNT(Query1[Sell-to Customer No_]),DISTINCTCOUNT(ALL(Query1[Sell-to Customer No_])))
And you can convert the datatype to percentage later.
I hope it works.
I get the following error when applying the formula: "The DISTINCTCOUNT function only accepts a column reference as an argument."
Do you have any idea of what could be wrong?