
1. Re: Totals for "A Only", "B Only," "A and B"
Stephen Rizzo Dec 12, 2016 1:37 PM (in response to william.boyd.0)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 Dec 12, 2016 2:17 PM (in response to Stephen Rizzo)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 Dec 14, 2016 7:55 AM (in response to william.boyd.0)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/understandinglodexpressions
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.

combinations.twbx 29.5 KB


4. Re: Totals for "A Only", "B Only," "A and B"
william.boyd.0 Dec 14, 2016 2:17 PM (in response to Stephen Rizzo)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