cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Percentage of column in Matrix - Cohort analysis

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:

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

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

9 REPLIES 9
Highlighted
Solution Sage

Helllo @nilst,

Can you please share the sample data file?

Thanks.

Highlighted
Frequent Visitor

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)

Highlighted
Microsoft

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

Highlighted
Frequent Visitor

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_])`
Highlighted
Solution Sage

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.

Highlighted
Frequent Visitor

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?

Highlighted
Solution Sage

Are you passing a column in DISTINCTCOUNT?

Error occurs because column is not passed in DISTINCTCOUNT.

Highlighted
Frequent Visitor

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

Highlighted
Frequent Visitor

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...

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors