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?

In a previous blog post I introduced the use of expander tables in Tableau when needing to handle a single row in multiple ways. You may have heard of expander tables before under the more generalised term of “scaffolding”.

In this blog post I’m going to cover some different use cases for expander tables. If you didn’t read part 1 yet, go ahead and have a skim through that…

Right let’s get into some use cases! For each case I’m going to describe the approach and benefits, as well as providing an example form the Tableau Community Forums or Tableau Public, that you can dig into in more detail.

1. To count a single row on multiple dates without using a UNION

I covered this use case in part 1 so pop back there for the detail. To recap: with an expander table you can turn a single row with an opened and closed date, into two rows. To do this you relate your existing table onto another table which has two rows, with relationship calcs of 1=1. This means every original row joins to both of the additional rows (as 1=1 is always true for the join). Then you can have a calculation that says “for copy 1 use opened date, and for copy 2 use closed date”. This allows you to count the row on both the opened date (as a +1) and the closed date (as a -1, say).

If you have more than two dates then add extra rows to the expander table (one expander row per date), and extra clauses to your calculations.

Benefits: with a UNION your data set is always duplicated meaning that you have to cater for that in other parts of your workbook that use the data (i.e. deduplicate it). With a relationship to an expander table your data will only be duplicated when you need it to be.

Example: here is the example from my VizIt Sydney presentation published to Tableau Public. The third view is the expander table version. The first view demonstrates why we need to duplicate the data, and the second view is a more traditional UNION-based solution.

2. To avoid issues from pivoting your data

Sometimes your data will look a little like this, where a measure is split out by a category or dimension:

In this case we don’t have an overall “sales” figure with a dimension for “region”. Instead we have sales for the East region in one column, and sales for the West region in another. Another example that often arises is where each year’s value is in a separate column (Sales 2023, Sales 2024, Sales 2025, etc). Unfortunately Tableau much prefers the measure to be in a single column, and there to be a row per category.

To get around this we can use the built in pivot option when building the data source:

And that will give us two new columns: Pivot Field Names (with our category), and Pivot Field Values (the measure). NB: Usually you would rename these, in this case to Region and Sales. And you would usually remove the word “Sales” prefixing the Region, I won’t do that in this example.

One problem you’ll spot above is that we have now duplicated Stock On Hand!

So whilst we can use Pivot Field Names (on Colour) and Pivot Field Values (on Columns) to create a stacked bar chart breaking Sales down by region. If we also want to show Stock On Hand, it is now double counted:

Instead we can use an expander table.

The table has a row per Region:

We need to relate that table to the original data set, with a relationship of 1=1. To do this we pick relationship calculation from each of the drop downs and enter in 1:

We can now drag Region onto Colour. Then we need a calculation for Sales which says “when the Region is East use Sales East, and when the Region is West use Sales West”, and we drag that onto Columns:

We still get our stacked bar chart of sales broken down by region. But now if we drag Stock on Hand onto the view it doesn’t double count as Tableau doesn’t need to use the expander table for that part of the view.

Benefits: similarly to avoiding a UNION, avoiding a PIVOT in favour of an expander tables means that additional data isn’t always duplicated. It is only duplicated when we need it to be. And that can help avoid having to deduplicate other values in your visualisation.

Example: Forum example.

3. Instead of Measure Names and Measure Values

In the example above you may have been thinking “I could just use the special Measure Names and Measure Values pills instead of a pivot or an expander table”. And you would be right:

With this approach the Stock On Hand isn’t double counted either. Nice!

But … yep, there was always going to be a but … the special Measure Names and Measure Values pills do have some restrictions. You can’t use them in calculations, they are harder to pass through dashboard actions, and you are restricted on sort options which can be a pain.

Using an expander table as shown in the previous section can give you your own “measure” and “value” columns, but in a way that you can use in calculations, actions and sorting. The example linked below goes into more detail.

Benefits: the special Measure Names and Measure Values pills are very handy but do have some restrictions. Those restrictions don’t apply to the normal pills you can get via use of an expander table.

Example: Forum example (scroll down further than the “best” answer, as an early post goes into the expander table option in more detail).

Further use cases

In the final blog post in the series I’ll cover the use of expander tables to:

• Create custom sub totals or grand totals
• Count a category or date in multiple groupings
• Densify your data in other ways

Until then, thanks for reading!

In a recent VizIt Sydney and Tableau User Group presentation, Fantastic Tableau tricks, and how to avoid them, I talked about expander tables and their benefits when working with data in Tableau. In this blog series I’ll cover:

• expander tables in more depth (part 1); and
• use cases they can help with (parts 2+)

But first up…

Why might you need to use an expander table?

When working with data in Tableau it’s not unusual to find that you need to treat a single row of data in more than one way in the same view.  A common example is where a row has both an opened date and a closed date, and you need to plot items opened and closed per date. Each item, or row, needs to be counted twice. Another example is where you have multiple measures per row, you need to use more than one on the same view, and a dual axis or the built in Measure Names and Measure Values pills aren’t flexible enough.

An expander table can help you to handle a single row in more than one way.

An example

Let’s use the example of support ticket data:

We have a support ticket number and for each of these there is a date opened and a status. When the status is closed we have a date closed.

If we want to plot the number of tickets opened and closed on any given date, we can’t do that easily with the data like this. For each ticket we only have a single row so we can only use the date opened OR the date closed. As the data stands, we can’t use both to count the ticket when it was opened and when it was closed.

You might be used to seeing date scaffold tables to help with this situation, pivoting the data, or possibly using a union like so:

The union (first black arrow) gives you two copies of the data, differentiated by two new fields – Sheet and Table Name (second black arrow).

The problem with these approaches is they often reshape the data in more situations than you want, or in ways that cause other impacts to your charts. For example, with the support ticket data, if we just count the rows in the now union-ed data set we will end up double counting support tickets!

Instead we can use an expander table:

Here I have related an “expander” table onto the support tickets (top black arrow); the expander table has two rows in it (Row Set A and B, highlighted with the diagonal arrow); and the relationship is on a relationship calc of 1=1, which is always true (bottom arrow, note: you choose “relationship calc” in the drop downs here and then enter 1 for each).

This relationship ensures that for every support ticket we can choose to have two rows. But that will only happen when we reference the Row Set from the expander table. In other cases the support tickets data will act as it always has, with no double ups. This ability to only have the extra rows when we choose to is a key advantage over a union or pivot, as it means calcs like the total number of tickets don’t double count by default.

Lets take a look at how we use the expander table when building a view:

Here I build a combined date. When the Row Set is A, the date will be the date opened. And when the Row Set is B, it will be the date closed. I also create a similar calc for Ticket Numbers: +1 when a ticket was opened (Row Set A), and -1 when a ticket was closed (Row Set B where status is closed):

We’re using one set of rows for opened, and the other for closed.

With this we can plot bars for both tickets opened (orange) and tickets closed (light blue). And unlike a union, we can still get the correct total ticket count (black arrow below) without having to adjust that calc … we only get the second set of rows when we needed it, thanks to the way relationships work.

Whilst this is a very simple concept it can be incredibly useful when your data is not quite in the shape you need in Tableau. Cases where you might otherwise turn to Measure Names and Values, a union, pivot, or complex calculations.

What if I can’t add another table / sheet?

Firstly, if you’re using a database connection you don’t necessarily need an expander table in the same database. You could try adding an extra connection in the Data Source pane, selecting a CSV or Excel and them using that extra connection:

You can also copy a simple text file with a header and X rows and from the “Data” menu “Paste Data as Connection” which will achieve the same thing.

And finally, if you are using a database, you could try adding a Custom SQL where the SQL you enter selects X number of rows from a table that you do have and converts to what you need, e.g. if you have a table “YourTable” that has a Location in it with values North, South, East and West, and you want an expander table with just two rows, you could use this Custom SQL:

SELECT CASE WHEN Location='North' THEN 1 ELSE 2 END AS RowNum
FROM YourTable 
WHERE Location IN ('North', 'South') 
GROUP BY CASE WHEN Location='North' THEN 1 ELSE 2 END

NB: This sort of Custom SQL approach does run the risk of reducing performance!

Use cases

In part 2 and part 3 of this blog series I cover six use cases for expander tables:

1. Count a single row on multiple dates without using a UNION
2. Avoid issues from pivoting your data
3. Instead of Measure Names and Measure Values
4. Create custom sub totals or grand totals
5. Count a category or date in multiple groupings
6. Densify your data in other ways

You can also check out the You Tube video at the start of this post (~16m50s to ~21m30s).