Percentage of column in Matrix - Cohort analysis

[nilst]

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 🙂

[v-huizhn-msft]

Hi @nilst,

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? 

Best Regards,
Angelia

[nilst]

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_])

[rajulshah]

Hello @nilst,

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.

Regards.

[nilst]

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?

[rajulshah]

Are you passing a column in DISTINCTCOUNT?

Error occurs because column is not passed in DISTINCTCOUNT.

[nilst · Accepted solution]

I found a more simple way to solve my problem. Showing the value as "Percent of row total" did the trick.

Thank you for your help.

[kiltannen]

WHile this wasn't exactly what I was trying to do  It did answer for me how to achieve what I needed - because I was trying to get to Column Percent...

[rajulshah]

Helllo @nilst,

Can you please share the sample data file?

Thanks.

[nilst]

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_]))

And MonthsSinceFirstPurchase:

MonthsSinceFirstPurchase = DATEDIFF(Query1[DateOfFirstPurchase]; Query1[Posting Date]; MONTH)