- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
Please advise a solution 🙂
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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_])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you passing a column in DISTINCTCOUNT?
Error occurs because column is not passed in DISTINCTCOUNT.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
Anonymous
| 08-21-2023 08:30 AM | ||
Anonymous
| 07-16-2024 08:00 PM | ||
06-12-2022 10:22 AM | |||
07-21-2017 12:09 PM | |||
10-18-2024 02:54 AM |
User | Count |
---|---|
84 | |
81 | |
52 | |
37 | |
36 |
User | Count |
---|---|
105 | |
85 | |
48 | |
42 | |
41 |