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

    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. 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 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 Smith John Smith 3
      John Smith Jim Johnson2
      John Smith Bob adams1
      John Smith justin jones1
      John Smith chris 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