2 Replies Latest reply on Oct 26, 2018 12:23 PM by Gabe DeWitt

# Counts of how many times an item appears in a dimension

I need to find a way to see the count of how often certain sales people work with one another in a data set. Essnetially i need to count how many times a distinct value appears in a dimension based on the ID of the item in a different dimension column. The data is sensitive so i cannot share the workbook but it looks something like the table below

 Sale ID Sale item Seller Name 112233 Ford John Smith 112233 Ford jim johnson 112233 Ford bob adams 445566 Chevy jim johnson 778899 dodge John Smith 778899 dodge bob adams 101010 Toyota chris clark 101010 Toyota jim johnson 101010 Toyota John Smith 101010 Toyota justin jones

I need to create a dashboard that shows a table of how often certain sales people work together. For example, I would expect that the table would look like something below...

 Seller Name Seller Name Count of Sales John Smith John Smith 3 John Smith Jim Johnson 2 John Smith Bob adams 1 John Smith justin jones 1 John Smith chris clark 2 Jim Johnson john smith 1 Jim Johnson Bob adams 1 Jim Johnson chris clark 1 Jim Johnson justin jones 1

So essentially, the john smith john smith count would be a count of all the sales they were involved in and then the John smith jim johnson count means that there were two sales where John smith and Jim johnson worked together (sale id 112233 and 101010). Any ideas? I'm thinking there is a calculated field that could solve for this

Thanks!

• ###### 1. Re: Counts of how many times an item appears in a dimension

Hi Zach,

The analysis you're trying to do is called a 'market basket analysis'.

In its simplest form it could be visualised by a cross-table.

And a common way to it is to make a self-joined datasource.

Please find the attached as an example.

Yours,

Yuri

• ###### 2. Re: Counts of how many times an item appears in a dimension

Hi Zach,