Forum Discussion
Custom column to count matches?
I have a table that contains customers, tickets, and notes.
Each customer may have multiple tickets. Each ticket may have multiple notes. So the data looks something like this:
What I need to do is create a chart that shows how many customers have X number of tickets, like this:
I'm thinking what I need to do is create a custom column that has a static count of the number of distinct tickets for each customer, so I can then use that as a category by which distinct customers can be counted.
I've tried experimenting with EARLIER to match up customer rows, and I've gotten frustratingly close but lost my progress and never was able to get what I needed. Can anyone help?
Hi RandomKitten,
Try this formula as a calculated column, please.
Column = CALCULATE ( COUNT ( 'table'[Ticket] ), ALLEXCEPT ( 'table', 'table'[Customer] ) )Best Regards,
Dale
3 Replies
So, is the Note column at all important in this?
Can you post the sample data as text?
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thank you for the link to the blog, Greg, I had looked for a way to paste the data into the original post but hadn't seen an option and was frankly too weary to bother figuring it out after having lost my original progress. Now I know for later.
The Note was included to illustrate that there might be duplicate rows for each ticket due to multiple activities on some tickets - which we also have to keep track of for efficiency, so I can't just scrub the data for duplicates.
Fortunately, with a little tweaking (changing COUNT to DISTINCTCOUNT) the other response seems to be working now!
Hi RandomKitten,
Try this formula as a calculated column, please.
Column = CALCULATE ( COUNT ( 'table'[Ticket] ), ALLEXCEPT ( 'table', 'table'[Customer] ) )Best Regards,
Dale
