Show Me How: Text Tables
Show Me How: Text Tables
Whatever you’re doing in Tableau then I think it’s fair to say that you’re likely to be using Text Tables (also known as crosstabs) somewhere in your reports, though never more so than for users first dipping their toes into Tableau. Love them or hate them they are here to stay.
Excel Pivot Tables or crosstabs are probably people’s first introduction to text tables and as such they have become the mainstay of the BI world as a way of quickly conveying important numbers at a glance, however they are often overused. Before you start with a text table make sure you ask yourself these important questions:
1. Is the end user initially looking for the actual numbers, or at an overall trend?
2. Do they need to know all the figures or are they looking for outliers?
3. Are the figures needed at this level of detail or is the user likely to need to drill down into the data?
4. Is the report likely to be printed? Or will it be used interactively?
If the answer to each of these questions is the former option then it’s likely that yes, a text table is an appropriate choice for showing your data, otherwise you might want to consider more visual chart types of showing your data to allow the user to determine trends, outliers and summaries and then interactively work with your report to find the detail themselves.
But you knew all that right? Otherwise you would be using the best visual analytics tool on the market?
The Show Me button for Text Tables in Tableau will always do a good job of trying to produce a text table from the elements (dimensions and measures) you are using in the view, but sometimes it may not do what you expect. How does Tableau decide where to drop the various elements to produce your Text Table.
Firstly let’s talk about Date Dimensions, Tableau will drop these onto the Columns pane. This is sensible, Tableau expects you will want to look across years / months and compare the values, having these going across the view makes sense.
What about Related Dimensions, Tableau will drop these onto the view along rows, in a hierarchy if it detects there is a relationship between the two elements in the view. Try this: using the Superstore Sales dataset provided with Tableau, drop Department onto Columns and Category onto Rows. Drop any old measure into the centre of the view. Now click the Show Me Text Table button, see what Tableau did? Clever huh:
There’s a relationship between Category and Department in the data, we haven’t told Tableau about it but Tableau looked at the intersections between the two dimensions in the dataset and knew it shouldn’t be presented as a crosstab but as a set of row level data. Try the same thing with two unrelated dimensions, notice the difference? Yes Tableau does lots of work you didn’t realise.
If you did as I suggested and dropped two unrelated dimensions on the view you will have seen a crosstab report, this is what Tableau will produce by default with Show Me using unrelated dimensions.
More than one measure? Show Me will automatically use the Measure Names, Measure Values pills to display those in the table – it will use the columns to do this, again this is typically we’re you’d expect to see these values right?
Many Beginner Tableau users don’t necessarily realise that they can recreate a text table in Tableau very easily with very little effort. Try it: Double Click these items from Superstore Sales in the order given: Category (Dimension), Region (Dimension), Sales (Measure).
Yes it’s that easy, you don’t even need to drag. Clear your view, and try it again with: Category (Dimension), Department (Dimension), Sales (Measure). Yes Tableau picks up related dimensions straight from the Double-click. Now click Show Me: Text Tables. So the order you double-click matters – but Show Me works out the best ordering.
This double click-order is especially important when it comes to quickly producing text tables. Try clearing your sheet, now try double-clicking these in order: Sales (Measure), Category (Dimension), Region (Dimension). Doing the measure first produces a bar chart instead.
If you want to convert this to a text table? Easy, and you don’t even need Show Me, just pick up the sum(Sales) pill and drop it onto the Label button, now drag Category from Rows to Columns. (Notes that this is a better option here than using Show Me, as the resulting table in Show Me is taller than it is wide, and fits on the screen, Show Me leaves Category on Columns, resulting in a scroll bar.
Want to introduce another measure? Pick it up and drag it onto the centre of the pane, this will quickly do what you need. In this case you don’t want to drag the pill to the Label button, doing so will label each cell with both values, not create a multi-dimensional crosstab as you expected.
Show Me How
Now let’s explore all these features in a short video that explains all the above concepts.
Other posts in this series
We’ll be exploring other Show Me charts in this series, check on the Show Me How Index to explore them.