3 Replies Latest reply on Aug 2, 2012 7:09 AM by Jonathan Drummey

# Calculate a "two-steps" aggregation in Tableau

Hi!

Is there any way in Tableau to aggregate data and then aggregate again the result (by another field)?

Example:

I have a list of customers and products they have bought:

CUSTOMER_IDPRODUCT_ID

1

111
2111
2123
7134
7151
8524
8624
8111

I want to have a table with count of distinct products for customer as first column, and number of customers with that number as second column.

Usually when doing it with SQL or Excel, I do it in two steps, first calulating how mant different products each customer bought, and then using this result I calculate how many customers bought each amount of different products.

In the example above the final result should be:

number of different products bought by customernumber of customers
1

1

2

2
31

That's because one customer (1) bought only one product,

two customers (2,7) bought two products each,

and one customer (8) bought three products.

I have tried a lot to do that in Tableau with no success. I would be very glad if there is such way.

Thanks!

• ###### 1. Re: Calculate a "two-steps" aggregation in Tableau

Hi,

# of Diff Products Bought by Customer: COUNTD([Product ID])

# of Customers: IF FIRST()==0 THEN WINDOW_COUNT(COUNTD([Customer ID])) END

In the view, put Customer ID on the Level of Detail Shelf. Then the # of Diff Products calc is brought onto the Rows Shelf, set to Discrete (a blue pill, so it will generate headers), and then click on the blue pill once more to uncheck Ignore in Table Calculations. Finally, the # of Customers can be put on the text shelf (or dragged over the Abc marks) to create a text table, and click on the green pill to set the Compute Using to Customer ID.

There are a couple of tricky bits in this kind of view: In order to have distinct counts of customers and products, both need to be in the overall level of detail (on Rows, Columns, Marks Card, or Pages Shelf) of the view, and in a view like this that's going to return overlapping results. So we use a table calculation with the IF FIRST()==0 to return only one result per partition. The next bit is that you are wanting to partition a measure (the customer count), i.e. generate headers, using the results of another aggregate measure (the count distinct of products per customer). Ordinarily Tableau will only partition based on non-aggregated dimensions. That's where the unchecking "Ignore in Table Calculations" comes in, it enables Tableau to partition by that calculation to generate the desired results.

• ###### 2. Re: Calculate a "two-steps" aggregation in Tableau

Works like magic! Thanks very much

• ###### 3. Re: Calculate a "two-steps" aggregation in Tableau

You're welcome!