3 Replies Latest reply on Oct 10, 2018 1:20 PM by Jim Dehner

# Count of Times dimension appears based on other 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. Essentially 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: Count of Times dimension appears based on other dimension

Hi Zach

see if this works for you - i copied the file and joined it to itself on the sales ID

It returns something like this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Count of Times dimension appears based on other dimension

Great idea! This worked perfectly, thank you!

• ###### 3. Re: Count of Times dimension appears based on other dimension

Thanks -