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.