2 Replies Latest reply on Jul 5, 2018 11:49 AM by Galen Busch

    Find Sum of Sales with Consumers of a specific Market Basket

    Brandon Storey

      Hi all,

       

      I am working on a project currently where I have 4 items. For these items, I want to find out which customers purchased a specific combination of the items and how much of that combination they purchased. For example, lets say I have 4 products A,B,C, and D.

      • Find only the customers who purchased product A.
      • Find only the customers who purchased product B.
      • Find only the customers who purchased product C.
      • Find only the customers who purchased product D.

      I was successfully able to do this. However, where I run into trouble is with the following:

      • Find only the customers who purchased product A and B.
      • Find only the customers who purchased product A and C.
      • Find only the customers who purchased product A and D.
      • Find only the customers who purchased product A, B, and C.
      • Etc.

      I am ultimately trying to just make a simple stacked bar chart of each scenario. How can I filter only the customers who purchased a certain combination of my items offered without including the other customers who may have purchased one or two of those items.

        • 1. Re: Find Sum of Sales with Consumers of a specific Market Basket
          Galen Busch

          Hi Brandon,

           

          A market basket analysis is what you're looking for. The best tutorial is here:

           

          Workout Wednesday: Market Basket Analysis

           

          The limitation with this method, is that you'll be confined to 2 combinations - A & B, not A & B & C

           

          Due to your relatively low number of members of your dimension, I'm afraid the best way to get there may be through a series of calculations.

           

          {FIXED [Customer ID] : max(if [Product] = 'A' then 1 else 0 end)} //1 if customer every purchased product, else 0

           

          {FIXED [Customer ID] : max(if [Product] = 'B' then 1 else 0 end)} //1 if customer every purchased product, else 0

           

          {FIXED [Customer ID] : max(if [Product] = 'C' then 1 else 0 end)} //1 if customer every purchased product, else 0

           

          {FIXED [Customer ID] : max(if [Product] = 'D' then 1 else 0 end)} //1 if customer every purchased product, else 0

           

          You can then create ((many)) calculations from this.

           

          if [Calc A] = 1 and [Calc B] = 1 then [Customer ID] end "A & B purchased"

           

          if [Calc A] = 1 and [Calc B] = 1 and [Calc C] = 1 then [Customer ID] end "A & B & C purchased"

          • 2. Re: Find Sum of Sales with Consumers of a specific Market Basket
            Galen Busch

            Alternatively, you could simply bring each of these 4 calculations to the filters shelf, and use these filters for specific combinations.