Visualizing Crosstab and Grand Totals

This is the sequel to an earlier post Enhancing Text Table with Visual Background.

This post is a tentative to help read crosstab better with visual hints. The colors I use here may look outrageous. But it provides a template to customize and create your own look and feel. If you do and like to share, please leave a link in the comment.

Please note that:
- Bars of the same color are congruent in scale.
- Main table values vs subtotals/grand totals don't compare. That's why we use colors.

Note that the color legend presented here is not based on any particular data set. It's solely based on the area of the crosstab such as main table, subtotals and grand totals by row and column.

Area Of Different Scale

Our example is a 2CDx2RD crosstab table, that is 2 column dimensions and 2 row dimensions. The technique can be extrapolated to tables of other dimensions.

A 2CDx2RD crosstab table with subtotals and grand totals can be divided into 9 different areas. It is defined by this formula: (See Zen Master Jonathon Drummey's post for reference.)

//1 main table
//2 column subtotals
//3 column grand totals
//4 row subtotals
//5 row-column subtotals
//6 column grand total of row subtotals or subtotal of column grand totals
//7 row grand totals
//8 row grand total of column subtotals or subtotal of row grand totals
//9 grand total of the entire table

Caveat: When there is a single non-zero value in a row or column, Max and Min are always equal. To filter such special cases, we use [Win Area]= Window_Max(Area) in the place of Area.

Scaled Bars

For each area, we first calculate the Max Sales

Window_Max(Sum(Sales))

Then we scale/normalize all the Sales by the Max Sales of the same area:

Thus all bars are based on Normalized Sales with different colors.

Bar Type

One has the option to show symmetric bars or horizontal bars, depending on one's preference. Bars are created using Gantt Bars.

Area Index

This parameter is created to illustrate that we can highlight each area individually.

Formatting Totals and Grand Totals

Tableau Desktop provides basic formatting for subtotals and grand totals, such as shading, fonts, and even renaming. This is used in combination with the above techniques.

Template

Feel free to download the demo viz. If you like to use it with your own data set, you can rename the four dimensions according to those in your data set. Then replace data source. That's all. If you have questions, pls leave them in comments.

(Refresh the page if you want to view the gif image multiple times. Or go to Tableau Public and click the button at the top-right corner.)

Jake and I collaborated on a dashboard. He told me that he learnt a way to create an in-place help page in Tableau. He first saw it at a conference somewhere and couldn't recall who the speaker was. So I am blogging here about it but the credit goes to somebody else. If anyone knows who the original creator is, leave a comment below.

The key idea is to float a semi transparent worksheet on top of the dashboard, where a help text box is strategically placed on top of each chart. This way, we can explain how to view each chart and what data points are important, etc. This worksheet is collapsible by a show/hide button.

Below I would like to show how this worksheet can be constructed.

1. Sheet with a single data mark.

Double click the empty space in Marks panel and add two single quotes. Make the null pill a text label. This creates a single null mark.
Set the view as "Entire View"

2. Create an show/hide button

Go to the target dashboard
Drag a floating vertical container to the dashboard, making it cover all the area of interest.
Drag the Single Null Mark sheet and drop it into the above container. Hide the sheet title.
Create an open/close button for the container and place the button at the top-right corner.