As a Tableau evangelist at my company, one of the most persistent challenges I face is the table. Tableau can do all sorts of things really well (especially once you get comfortable with things like table calculations and parameters), but when it comes to making tables more than just numbers, Tableau is fairly limited. Excel offers way more flexibility in terms of targeted and conditional formatting. Yes, you can perform some data prep judo to bring the data into Tableau in a way that facilitates more formatting options, but those steps aren’t straightforward and aren’t applicable to every data set.
And while visualizations of data generally provide greater insight than simple tables, there are times when a table of numbers is more appropriate. There are also times when your boss just wants a table, but wants it to highlight specific metrics. In these cases, I need to resist the urge to produce one of the rainbow-colored Great Walls of Data that are way too prevalent in businesses today. But how can I make Tableau do what I (and more importantly, my boss) want it to? The steps below are what I’ve employed on these occasions, to mostly positive reception.
Step 1: Figure out what the key metrics are.
This may seem obvious, but when presenting a bank of numbers, it’s important to identify what the primary vs. secondary (or supporting) values are. This also guides me in determining which metrics to chart and which ones to keep as numbers.
Step 2: Build the Table
Tableau makes it really easy to build a table. But when you want to conditionally format a couple of those numbers (Y/Y Profit and Y/Y Sales in the example below), things quickly hit a wall. Not only can I only choose one of these metrics for the color shelf, but the color is applied to all of the measures and not just to the specific measure. Bummer.
Step 3. Enter the Mako Matrix
Some of the Zen Masters (among them Joe Mako and Jonathan Drummey) have written and spoken about how we should not feel constrained by the default functionality of our measures and dimensions, so long as we understand what the different pill types do. In this case, our formatting options are constrained by the fact that we brought our measures over as green pills. To break free of these constraints, we want to choose the blue pill. We might not see how deep the rabbit hole goes, but we’ll discover just enough formatting freedom to build the table view we want.
If you select the context menu (the little downward-pointing triangle) on any of the green measures, you’ll see a choice for changing it to discrete. Once you do that, the pill turns blue. Now you can decide which measures you want to treat as discrete and which ones you want to treat as continuous, all inside of the same view.
This brings with it some pros and cons:
On the pro side, you can individually format certain measures. For example, in the view below I chose to make the 2013 figures blue and the 2012 figures grey. Nothing fancy, but just a nice visual cue to differentiate the years. The formatting of discrete pills, unfortunately, is pretty binary; I can only select one color or another from a drop-down vs. having the color be a continuum or based on the values themselves (e.g. red is bad, green is good).
This is where the distinction of primary vs. secondary metrics comes in. Since I wanted to draw attention to the Y/Y metrics, I can keep those as green pills and thereby retain all of the glorious formatting options Tableau provides to measures. In the example below, I chose to make the Y/Y Sales measure a simple grey bar (adding color wouldn’t provide any more insight than the sorted bars already communicate) and I chose to format the Y/Y Profit measure on a simple red/green continuum.
Again, nothing fancy, but now I have a table that leverages the power of visuals to communicate the key metrics (e.g. the Small Business segment had the second largest Sales growth but virtually no growth in profit, etc.) but also provides the relevant supporting numbers in the same table so that users don’t need to reference a tool-tip or another table for them. This is especially useful when the chart is being used inside of slides or emails, i.e. when the interactive benefits of Tableau are not available.
The main con I have found with this approach is that you can’t total the discrete measures. When I turn on column totals, the spaces below the discrete pills are blank as Tableau can’t aggregate dimensions. While I suspect there may be some cool ways to get around this using table calculations, what I find works very well is to create duplicated version of the worksheet (but only one row) and then use a dashboard to build the final view.
Step 4: Using a Dashboard to complete the View
Once I have my final table/chart view built, I just duplicate it and remove the dimension that is used for the row-level detail (e.g. Customer Segment in this example). I often create a simple dimension with just the string “Total” to use where the other field used to be, just so everything will line up nicely.
Next I create a new dashboard tab and then drag these two sheets in. I like to use a Vertical container so that the two charts hug each other, creating the illusion of a single chart. After that it’s just a matter of a few formatting tweaks to get everything lined up nicely. In particular:
1. Hide the Title and Row Header Labels on the Total sheet. You only need to show these once.
2. De-select ‘Show Header’ for the two green measures on the Segment sheet. Just like you only need to see the title and column headers once, you only need to see the axis labels once. You could keep the top ones exposed and hide the labels on the Total sheet, but I prefer having the axis labels at the very bottom as it further strengthens the illusion of a single chart.
3. Drag the column divider of the first column on the Total sheet so that it lines up with the top chart.
And there you have it. This may seem like a lot of steps, but once you know what you’re doing (and why), this whole process takes only a few minutes.
Conclusion: The Final Viz
I’ve published my workbook so you can look under the hood, in case my explanations above weren’t sufficient. I’ve also added a few “finishing touches” to the initial dashboard, to satisfy some of my own perfectionist tendencies. 🙂
- I made the Y/Y Profit measure a circle vs. a bar, mainly to ensure that the Segment and Total sheets lined up better. Since one of the segments had negative profit, the axis extended on both sides of the 0, whereas the total only went positive. This just didn’t look good to me.
- I added a table calculation to the segment chart to hide it in the event the user filters to just one segment. If I don’t do this, then when only one segment is selected you have the same numbers twice (once in the segment chart and again in the total chart). Not horrible, but unnecessary. This also required me to move the Total sheet above the Segment sheet so that the title and column headers are always visible, even when the Segment sheet is hidden.
Thanks for reading.