In previous blog posts I introduced the use of expander tables in Tableau when needing to handle a single row in multiple ways, along with three specific use cases.

In this post, part three of the series, I’m going to cover three more use cases. Let’s take a look…

4. To create custom sub totals or grand totals

Here I have a view showing sales by country and region. I’ve turned on grand totals and sub totals for rows so that I get the overall total and a sub-total per country:

If I only wanted the breakdown to region for the US, with Canada shown as a single combined row, then I could create a calc to only return region for US. But if I still want a sub-total for the US I would also get a sub-total for Canada which is unwanted:

In this case I can use an expander table with three rows:

Let’s take a look at how we use this in the view. NB: the final view won’t look quite like this but it’s useful to see (and test!) what’s happening behind the scenes:

What you can see highlighted yellow is that

• “Row Group” A = the detail level (region or country)
• “Row Group” B = the sub-total row for the US
• “Row Group” C = the grand total row

Pointed out in red there is also a “Region 3 Group” calc which is ensuring that the sub-total row for the US is “less” than the detail row for Canada (“02″ vs “03″).

If I now remove “Row Group” from the view, and hide “Region 3 Group” I get the view I wanted:

I’ve added row banding so that the sub-total and the grand total have different backgrounds. And you’ll see that the “Region 3″ calc has similar logic to the previous calc to return region country or sub-total / grand total text. Nice!

Benefits: more control over sub-totals and row ordering, as long as you can write a calc to do it. As noted above a good trick is to include all columns in your view to test your calcs before gradually reducing down to the ones you need on the view.

Example: forum example.

5. To have a category or date in multiple groupings

Sometimes you want to include a single row into more than one grouping or category. People often try to use IF / THEN / ELSE logic for this. In this example the analyst wants to treat any Tomatoes as both a fruit and a vegetable:

IF [Category] IN ("Apples", "Tomatoes", "Oranges")
THEN "Fruit"
ELSEIF [Category] IN ("Carrot", "Cucumber", "Tomatoes")
THEN "Vegetable"
ELSE "Other"
END

Unfortunately, this doesn’t do the job. Once a row in the data has fallen into the IF logic (fruit) it won’t also be able to count in the ELSEIF (vegetable). You can’t count the row twice.

Instead, you can use an expander table to give yourself two rows for every single Tomatoes row, and handle version 1 as fruit and version 2 as vegetable. You will need to ensure that you have a calc that can be used to filter out version 2 of all the other rows.

A similar example is where you want to show sales today and this month. Any records for today should appear in both totals.

Benefits: you won’t try to achieve the impossible in a calc (please do feel free to prove me wrong of course!).

Example: Forum example.

6. Data densification

This used to be more prevalent when polygons were being created to make certain advanced chart types, but it can also apply when doing advanced charting using map layers.

If you need to plot a square for each (singular) data point in a data set, then you can relate to an expander table with 5 rows.

• Row 1 will = bottom left
• Row 2 will = bottom right
• Row 3 will = top right
• Row 4 will = top left
• Row 5 will = bottom left again

You’ll use calculations to determine the X/Y coordinate of each. E.g. if your data point has coordinates N, M and you want that to be the bottom left of a square that is 1×1…

• Row 1: X=N, Y=M
• Row 2: X=N+1, Y=M
• Row 3: X=N+1, Y=M+1
• Row 4: X=N, Y=M+1
• Row 5: X=N, Y=M

Putting X/Y on Rows and Columns, an identifier on Detail, picking Polygon mark type, and then placing Row Num on Path should give you a filled 1×1 square for each of your data points.

Nowadays if you can use GEOMETRY/GEOGRAPHY types or shapefiles then it’s best to do so. However, this technique can come in handy if you’re drawing advanced charts yourself using map layers.

Benefits: gives you more control over what Tableau can “draw” by allowing you to create additional marks for data points.

Examples:

• Tableau Polygons 101. Aka how I learned to draw triangles | by Brittany Rosenau | Medium
• Advanced tables using map layers (Ludovic Tavernier) - whilst this example doesn’t need an expander table, imagine extending it to be able to draw more per data point.

Closing comments

I hope you’ve enjoyed this series of posts and examples. Expander tables have been a key part of my Tableau toolset for a while now. If you haven’t used them, then I’d highly recommend considering them in the future for use cases like those I’ve covered. And I’ll leave you with the question: how else could you make use of expander tables?