Chart Matrices – How to create a grid from a dimension

Posted by on Oct 8, 2012

Sales by StateWhen you add a dimension in Tableau, you chop up your data either by columns, or by rows but there may be times in which you want to use a single dimension to slice your data by columns AND rows to produce a grid-like visualisation.

Whilst probably not usual best practice, this output can be useful when you have many dimension members and want to see them all in one page without squashing up your text too much, or to replicate the matrix of scatter plots type views that is possible in Tableau when plotting several measures. This post examines the steps to complete a simple example using the Sample Superstore Sales data that ships with Tableau.

The first step is to identify how many members are in your dimension and to decide how you would like your grid to be configured. For example if you have 9 members then a 3×3 grid might be most appropriate, or if you have 10 then perhaps 2×5. Next, you need to assign each member to a grid position by setting ‘X’ and ‘Y’ coordinates to each. You may find it useful to draw out a simple table to help with this. In the example shown on the right, we have the 4 members of the ‘Region’ dimension set out in a 2×2 grid. Central needs to have an X-coordinate of 0 and and Y of 0, East needs to have an X-coordinate of 0 and a Y of 1, and so on. Grid drawing

One way to do this is to create a Calculated Field and use IF or CASE statements to assign each member its X and Y coordinates but this can be tedious if you have any more than just a handful of members to assign. A better way is to make use of the INDEX function and a bit of simple mathematics so that Tableau assigns the coordinates for you.

The INDEX function in Tableau returns the order in which data is being displayed – in this case the order of the Region members. Central is 1, East is 2, South is 3 and West is 4. To generate our X-coordinates, we want a calculation that will turn 1 and 2 into 0s and 3 and 4 into 1s; this can be done by subtracting the index result by 1, dividing by 2 and rounding down the result e.g. 4-1 = 3, 3/2 = 1.5, 1.5 rounded down is 1. In Tableau, this is written as

int((index()-1) / 2)

For the Y-coordinate, the calculation must turn 1 and 3 into 0s and 2 and 4 into 1s. This is accomplished by subtracting 1 and taking the modulo of 2. In Tableau this is written as

(index()-1) % 2

Compute usingWe now have our two mathematical expressions and we can create two calculated fields using these formulas, one for X-coordinate and one for Y-coordinate. Bring these fields into your data pane, one on the Rows shelf, and one on the Columns shelf, add your dimension (Region) to the colour or label or size shelf and add then your measure, e.g. SUM(Sales). To tidy up, you can remove the coordinate headers (click on the pill dropdowns and unselect ‘Show Header’). Remember that these calculated fields are Table Calculations and you will have to set the compute method to the dimension you are using; in this case Region.

To do this for other dimensions with a different number of members, simply edit the Calculated Fields replacing divide by 2, and modulo 2, with the number of rows and columns in your desired table shape. Also remember to change the Table Calculation to use the new dimension. If you want to get really flash, then you can create a parameter to select the desired dimension, and calculate the table dimensions automatically using the square root of the number of members!

Play around with the interactive viz below, download and reverse-engineer, or contact The Information Lab for more information.

 

4 Comments

  1. Nice one Robin – nice to see a solution to this as it gives a lot more control over layouts that are too long or too tall

  2. Thanks, Tony. Worked it out in the end!

  3. Can this technique be applied to produce a calendar view for 1 year as a 3×4 or 4×3 matrix with profit ratio per day on a color shelf?

    • Sure it can! Create your 12 charts using the month as a dimension and then set up your x and y calculation to use the month, add x and y to the columns and rows and move month to the level of detail shelf

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>