Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

cohort-distinct-count.PNG

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:

 

result.PNG

 

 

Please advise a solution 🙂

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

 

solution.PNG

solution2.PNG

 

 

View solution in original post

9 REPLIES 9
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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

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

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.

 

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?

Are you passing a column in DISTINCTCOUNT?

 

Error occurs because column is not passed in DISTINCTCOUNT.

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.

 

solution.PNG

solution2.PNG

 

 

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
Super User
Super User

Helllo @nilst,

 

Can you please share the sample data file?

 

Thanks.

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)

sample-data.PNG

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors