Count of group dimensions item in Tableau

Question

I'm fairly new to tableau and I'm having the following issue. Below is a sample of the data I'm using.

Customer No | Item
___________________
 1            A
 1            B
 2            A
 3            A
 4            A
 4            B
 5            B
 6            A

I'm trying to get a count of how many customers bought Item A and B. So far I tried doing a separate group by combining A and B but I get the total result of 8. I also tried doing a calculation and I'm getting the same result of 8. Can someone please point me to the right direction on how to get this result. Thanks!

This is the result I'm trying to get:

Item| Count
A             5
B             3
A and B       2

Answer 1

I recreated your exact dataset and pasted it into Tableau so you could see a couple of examples.

Here's how you can see the number of customers who purchased an individual item, plus the number of customers who purchased both items.

Your calculation will be:

IF { FIXED [Customer No]: COUNTD([Item]) } = 1 THEN
    [Item]
ELSE
    'Both A and B'
END

And you'll need to set your view up to look like this:

Below are ways you can see when both items were purchased.

1. Boolean OR The calculation you'll want to use is:

   ATTR([ITEM]) = 'A' OR ATTR([ITEM]) = 'B'

And you'll want to set up your view to look like this:

2. A, B or Both If you would like a bit more specificity in your result, you might try:

   IF ATTR([Item]) = 'A' THEN 'A' ELSEIF ATTR([Item]) = 'B' THEN 'B' ELSE 'BOTH' END

Replacing the previous calculation with the new looks like this:

3. More than 1 item If the specific items purchased don't matter, you could use this logic.

   COUNTD([Item]) > 1

Replacing the previous calculation with this one would look like:

4. More than 1 Item using a window function (probably overkill) The calculation you'll need to use is:

   WINDOW_COUNT(COUNTD([Item]))

Because this is a Window function, we'll need to specify how it's calculated across our dimensions. To do this click the down arrow on the right-hand side of the pill and select Edit Table Calculation...

You'll then need to set these settings:

I'll add the calculation we created in the first example ([A and B]) to the filter shelf and select True. That should give you something that looks like:

5. More than 1 item using a Level of Detail expression The calculation for this example is:

   { EXCLUDE [Item]: COUNTD([Item]) }

You'll view should look like:

As you can see Tableau is quite flexible. Hope these examples were helpful!

Answer 2

You might want to use Tableau’s set feature to approach problems like this.

For example, right click on the field [Customer No] in the data pane (i.e. left sidebar) and choose the “Create Set” command. Click “Use All” at the top of the set panel and then click the Condition tab. Define the set using the condition MAX([Item] = “A”). Name the set “Customers who bought A”.

Similarly, create a set of customers who bought item B. You can then select both sets in the data pane, and create a combined set to be the intersection, that is, customers who bought both an item A and an item B.

You can think of a set as either a mathematical set of the members of a field that belong to the set (i.e. a set of customer ids) or as Boolean function defined for each data record in the data source indicating whether that data record is associated with the set (i.e. a Boolean function that operates on transactions to say whether the associated customer ID is in the set. A key to keep in mind for the condition formulas used here is that the condition is an aggregate formula, operating on a block of data records for a customer ID to determine whether the customer ID is in the set.

Once you have defined your sets of interest, you can use them in many ways - in calculated fields, as filters, as dimensions on shelves in a visualization, in set actions, to combine with other sets ...

To define a measure that counts the customers in a set, create a calculated field such as “[Num A Customers]” as COUNTD(if [Customers who bought A] then [Customer ID] end) Do the same for whatever other sets you are interested in. Then you can use those measures (probably with Measure Names and Measure Values) to make your viz.