Whether you are new to visual analytics, a seasoned veteran or somewhere in between you’ll know that for many data consumers, Excel is their happy place. Tables of data are familiar friends and who needs pretty charts anyway?
If you’re reading this blog, it’s fairly likely you understand the power of visually representing data and it’s also fairly likely that on presenting a beautifully crafted and well thought out dashboard or report the first question your end user has asked is “where do I click to export to Excel?”, often with an additional “for further analysis” for good measure.
Data and visual literacy is in my opinion, very much an issue of confidence. Charts can be intimidating to someone with little or no exposure to them, and so a key part of an analytics consultant’s role at Biztory is to help boost data confidence and to take users on a journey.
Over the years, the most effective method in my experience has been to give your audience what they want. If they’ve asked for a table of numbers, then give them a table of numbers. Build their confidence, give them something familiar…but throw in some extras.
Building crosstab tables in Tableau
It’s a simple process to build a crosstab table in Tableau. In the example below we’re using the fictional Superstore dataset - this data ships with all versions of Tableau - to show Sales by Region by Month. I’ve added a filter to allow the user to select a year and hey presto, a table of numbers, just like my business user asked for.
We’re done right? Well, not quite.
Let’s start by showing the table some love. We can increase the size a little to make it more readable (the ctrl-up arrow shortcut works brilliantly for this) and we can also hide field names for columns and rows and format the month names to an abbreviation. I’ve also removed tooltips as they have no context to add.
It looks better already, but let’s assume our end user is looking for patterns in the data. Adding some colour will help. In addition to text, let’s add Sales to colour and change the mark type to a square. I’ve chosen a blue (#436bd7) that’s in line with our company branding as a five-step gradient. I’ve also added in a light grey border via the colour card.
Our end user now has a table of numbers, and, using colour, we’ve helped bring the data to life and hopefully answer a follow up question. We could at this point add row and column totals but maybe there’s a way to add some visuals into the mix.
Bridge the gap: Add visualisations to your table in Tableau
Enter the marginal histogram. Sounds fancy and complicated but essentially, it’s a couple of simple, nicely formatted bar charts arranged to work with our table of numbers. We’re aiming for something like the dashboard below.
Add bar charts to your table in Tableau
To make the vertical bar chart we create a new worksheet. Put discrete month of order date on columns and sum of sales on rows. Make it a quick table calculation to give us a percentage of total. Change the mark type to a bar, add mark labels (format to 1 decimal place if you wish) and to keep it on brand, I’ve just changed the colour to match the blue in my crosstab table. Remove all gridlines and remove the axes headers so you get a “clean” set of bars.
To make the horizontal bar chart, simply duplicate the vertical bar chart worksheet and swap the axes. Replace the discrete month with region on rows and then use ctrl-up arrow to increase the sizing slightly.
We now have all the elements we need to build out our marginal histogram in a new dashboard.
To get a basic idea of layout I’ve set the dashboard to 1000 x 400 and simply dropped in the three charts. I add the table of data first (pausing briefly to remove the container holding the legend and the filter), add the vertical bar chart above it and then the horizontal bar chart to the right of both charts. In each case I’ve set the charts to fit the entire view. It’ll look something like this:
Hide chart titles in your table
From there it’s a simple matter of hiding chart titles and then resizing and padding the objects. I make the vertical bar chart about half the height of the data table, and then make the horizontal bar chart about a quarter of the width of the other two visuals.
Adjust padding for your Tableau table
I then add some left inner padding to the vertical bar chart (make sure you uncheck the all sides equal box) – about 80 pixels should do the trick. For the horizontal bar chart I do the same sort of thing but with top inner padding of 145. Play around with the sizing and the padding if you’re following along and you should get something that looks like this:
This gives my end user a lot more information and context at both the month and the region level, and it hopefully uses visuals in a non-scary and complementary manner.
Give users the option to toggle bar charts on & off in Tableau
That said, I still want to give my end user a greater feeling of control. What about the ability to toggle the bar charts on and off?
When I build out a user facing dashboard like the one above, I always use containers, and I generally follow Curtis Harris’s technique of a single floating container the same size as the dashboard as the starting point to house all other containers. By building a dashboard out in this fashion and by paying attention to naming the objects in my item hierarchy (see below) functionality like dynamic zone visibility is a breeze.
Dynamic zone visibility is a feature release in Tableau 2022.3 that allows you to show/hide dashboard items based on a Boolean (true/false) parameter or field. In the Layout pane for each dashboard object there is an option to control visibility using value.
To test it out, create a Boolean parameter (I’ve called mine Show Margins) and then choose the objects you wish to control. By naming my objects in the item hierarchy I can quickly see I need to control two objects – my vertical bar chart and the vertical container housing my horizontal bar chart. The reason I select the container not the chart itself is to ensure that when I toggle, the data table will fill up all the available space in my main contents container.
And now by changing my parameter value I can show…
…or hide…
…my bar charts.
For the final version of my dashboard (see below) I’ve replaced the parameter and the filter objects with some visual toggles that hopefully further enhance the user experience. If you’d like to explore the techniques used to achieve this in more detail then please feel free to download the workbook from my Tableau Public page.
I hope this helps you start to bridge the data confidence gap with your own users!