Creating a Compact & Ordered Table in Tableau

The Index function is considered by most to be just for the Tableau advanced, maybe even jedi among us primarily because it’s a table calculation which doesn’t appear in ‘Quick Calcs’. Even worse it requires the user to venture in to the ‘Advanced’ table calculation mode to really get it to function how you want.

But as we saw from Tom’s post on understanding table calcs we believe that using the advanced mode should be the rule, not the exception. In fact I’ll bet that those of you who are still bemused by table calcs, because you just don’t get why that running total isn’t giving you the numbers you expected, getting to know the Index function will help you get back on track. By using Index to physically place data points we can better understand what’s going on when we address and partition dimensions. So let’s get started…

What we’ll set out to do is create a table (seen at the top) which lists customer states by region. Obviously in this case we aren’t going to reveal any big secrets hidden in our data but it will give us a nice compact table useful for filters and dashboards where space is a premium. Another great example would be a table of sales representatives and which states they operate in.

The first move 95% of people will do when asked to create a table of states, split by region, is to put Region on the columns shelf and Customer State on both Rows and Text.

This sort of creates what we're after....but it doesn’t look very good. Obviously New York will only exist in the row ‘New York’ leaving gaps above and below if a neighbour state doesn’t exist in a region. In other words we have too many rows which means we lose customer state from the rows shelf.

As we are now faced with a table of overlapping text we need to put something more useful on Rows, and there’s no surprise for guessing it’s the Index function. Index is easy to create as it’s just a calculated field containing the formula INDEX() and I’m assuming you’ll also name it ‘Index’.

Adding the new Index field to the Rows shelf doesn’t really get us much further, we just get the number 1 added to our table (if you get an axis switch the Index pill to be discrete rather than continuous). What’s important for Index is how we address and partition the table calc.

Pull down the Index pill’s menu and select ‘Edit Table Calculation’. By default now you should always be selecting ‘Compute using: Advanced’.

At this point as long as you just move customer state across to ‘Compute Using’ (or as we like to refer to it as the addressing fields) your table will become a nice compact list of states by region. My preference however is to go one step further and also bring region across as this allows me to take further charge of how the calculation will behave, and after clicking OK, specify that Tableau should compute using ‘Customer State’ and restart the numbering every ‘Region’. In order words for each region count consecutively the number of states.

Spurred on by this new sense of confidence you may now even wish to go back into the Advanced table calculation window and set the ‘Order Along’ field so that, for example, the states are listed in descending order of the sum of sales.

So why exactly do I think this function is so useful? Well by assigning a number to each data point and using the table calc on either rows or columns we have the freedom to position our data points exactly where we want them. A great example can be seen on the final sheet of the example workbook where each column of shapes is centred behind the line chart. There are also many uses of the Index function on my latest Tableau Public viz: Piers v King.

Here’s the workbook.

Want to keep up to date with tips and tricks from The Information Lab? Sign up to our newsletter.