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

    Count of Times dimension appears based on other dimension

    Zach  DiSalvo

      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 IDSale itemSeller Name
      112233FordJohn Smith
      112233Fordjim johnson
      112233Fordbob adams
      445566Chevyjim johnson
      778899dodgeJohn Smith
      778899dodgebob adams
      101010Toyotachris clark
      101010Toyotajim johnson
      101010ToyotaJohn Smith
      101010Toyotajustin 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 NameSeller NameCount of Sales
      John SmithJohn Smith3
      John SmithJim Johnson2
      John SmithBob adams1
      John Smithjustin jones1
      John Smithchris clark2
      Jim Johnsonjohn smith1
      Jim JohnsonBob adams1
      Jim Johnsonchris clark1
      Jim Johnsonjustin jones1

       

      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!