4 Replies Latest reply on Dec 14, 2016 2:17 PM by william.boyd.0

    Totals for "A Only", "B Only," "A and B"

    william.boyd.0

      Hello,

       

      I have my data formatted as follows:

       

      Customer IDCustomer NameProduct

      1

      MarkA
      1MarkB
      2JaneB
      3EllenA
      4SusieC
      5KarenA
      5KarenB
      5KarenC

       

       

      I can currently get a total distinct count of customer ID's per product, such as "A=3 distinct customers". What I want to be able to do is show the amount of distinct customers in all possible combinations of products. So it should display the following:

       

      Product CombinationsCount of Distinct Customers with the product combination
      A Only

      1

      B Only0
      C Only1
      A and B1
      A and C0
      B and C0
      A, B, and C1

       

      Would anyone be able to assist me in displaying these different totals?

      Thank you!

        • 1. Re: Totals for "A Only", "B Only," "A and B"
          Stephen Rizzo

          Fun question . Create a field in your data set that assigns each customer ID to its product combination, using a FIXED level of detail (LOD) expression. Personally, I would create a new field that assigns each product a power of two (A=1, B=2, C=4, etc.), then sum that field in a new calculation (the FIXED LOD) to get a unique ID for each customer's product combination. You could use aliases to make the numbers have the labels that you want. Finally, use this new field (converted to a dimension) to count the distinct number of customer IDs assigned to each of your product combinations.

          • 2. Re: Totals for "A Only", "B Only," "A and B"
            william.boyd.0

            So would I do something like{FIXED [Customer ID] : ([Product])}? I'm unfamiliar with the FIXED equation. And are you suggesting to create A=1, B=2, C=4, AB=6, AC=8, etc?

            • 3. Re: Totals for "A Only", "B Only," "A and B"
              Stephen Rizzo

              In order to solve this problem, you need at least a basic understanding of Level of Detail expressions. You can read more about those here:

               

              https://www.tableau.com/learn/whitepapers/understanding-lod-expressions

               

              As for the solution:

               

              Suppose you assign A=1, B=2, and C=4 to a new field called [Product Code]. Then notice that if you add up all of the product codes for each customer, the resulting number will tell you exactly which combination of products the customer had. For example, Mark had products A and B, so SUM([Product Code]) for that customer would be 3. The only way to get a SUM([Product Code]) of 3 is to have products A and B. So if you put just [Customer ID] into the Rows shelf and put SUM([Product Code]) as text marks, you get the number corresponding to whatever product category each customer ID is assigned to.

               

              Now we want to use SUM([Product Code]) as a dimension / category as opposed to a measure, and count how many customer IDs have that code. Your marks are going to need to be COUNTD([Customer ID]), but first you need some way to turn SUM([Product Code]) into a dimension. That is where FIXED LOD expressions come in. We write the calculated field

               

              [Combination Codes] : {FIXED [Customer ID] : SUM([Product Code])}

               

              What this does is tell Tableau to calculate SUM([Product Code]) as if only [Customer ID] was on the Rows shelf, then go through each row of your data and assign  as a value in [Combination Codes] the SUM([Product Code]) associated with the corresponding [Customer ID]. Tableau will then allow you to use this new field as a dimension, so you can aggregate customer IDs by [Combination Codes] values.

               

              With a bit of reformatting (and a blend in case you wanted all of the codes, not just ones with data), you get the attached workbook.

              1 of 1 people found this helpful
              • 4. Re: Totals for "A Only", "B Only," "A and B"
                william.boyd.0

                Thank you for the assistance! That was very helpful.

                 

                I ran into an additional problem where a customer could have more than one of each product, so if they had 2 of product A and 1 of product B, the SUM would no longer be correct. For anyone referencing this post, here was the solution.

                 

                I created a calculated field called "Combination Calculation"

                 

                IF [Product]="A" THEN 1

                ELSEIF [Product]="B" THEN 3

                ELSEIF [Product]="C" THEN 5

                END

                 

                I then created a calculated field called "Different Combinations" very similar to yours, but included MIN to resolve the issue of customers having more than 1 of a given product type.

                 

                IF { FIXED [Customer ID]:SUM({ FIXED [Customer ID], [Product]:MIN([Combination Calculation])})}=1 THEN "A Only"

                ELSEIF { FIXED [Customer ID]:SUM({ FIXED [Customer ID], [Product]: MIN([Combination Calculation])})}=3 THEN "B Only"

                ELSEIF { FIXED [Customer ID]:SUM({ FIXED [Customer ID], [Product]: MIN([Combination Calculation])})}=5 THEN "C Only"

                ELSEIF { FIXED [Customer ID]:SUM({ FIXED [Customer ID], [Product]: MIN([Combination Calculation])})}=4 THEN "A and B"

                ELSEIF { FIXED [Customer ID]:SUM({ FIXED [Customer ID], [Product]: MIN([Combination Calculation])})}=8 THEN "B and C"

                ELSEIF { FIXED [Customer ID]:SUM({ FIXED [Customer ID], [Product]: MIN([Combination Calculation])})}=6 THEN "A and C"

                ELSEIF { FIXED [Customer ID]:SUM({ FIXED [Customer ID], [Product]: MIN([Combination Calculation])})}=9 THEN "All"

                END