Regular #WOW participant, Caroline Swiger, set the guest challenge for community month this week, to recreate a visual table in a single sheet. She was heavily inspired by this Super Advanced Tables viz built by my colleague Sam Parsons which he discusses in this YouTube video. This was a concept I’d been meaning to try for a while, so having this set was ideal, as I now get to try it out and blog about it 🙂

I think the easiest way to approach this blog is simply column by column. When I tackled this initially, I ticked off as much as I could remember to do initially, and then referenced Sam’s video when it came to building the bars. As a consequence of that, I did then have to add a field that meant I had to adjust all the existing columns I’d made. If you follow this blog from start to finish, you shouldn’t need to do that.

This table revolves around utilising what I refer to as ‘fake axis’ to allow you to use different mark types other than text within each cell of the table. All of the columns in this table make use of a MIN(0) measure to act as the ‘fake axis’. Whilst we can just type MIN(0) into the Columns shelf each time, we’ll give these measures a specific name relating to the data being presented in each column, so we can easily find them on the Marks card if ever we need to make an adjustment.

Initial Set Up

Create a new field

Y-Axis Position

MIN(0.5)

Add Sub-Category to Rows and add Y-Axis Position to Rows as well. Having a measure on the Rows shelf is necessary for when we come to build the bar chart columns, and I’ll explain why in that section. Edit the Y-Axis Position axis to be fixed from -1 to 2.

Sales Rank Column

The main focus of the data is for the latest year values. So we need to identify various measures relating to just this year.

Latest Year

{MAX(YEAR([Order Date]))}

For the data I’m working with, this returns the year 2023, and from this we can then determine

Current Sales

IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END

Format this to $ with 0 dp.

Apply a Sort to the Sub-Category pill on Rows based on this field.

Create a new field

Sales Rank

RANK(SUM([Current Sales]))

and a field for the ‘axis’

Sales Rank Axis

MIN(0)

Add Sales Rank Axis to Columns. Change the mark type to circle, increase the size a bit. Change the Colour to dark grey.

Add Sales Rank to the Label shelf and edit the table calculation so that it is computing by Sub-Category. Align the text middle centre and bold the text.

Current Sales Bar

Create a new field

Current Sales Axis

MIN(0)

and add to the Column shelf. Change the mark type of this to a bar. Remove Sales Rank from the Label shelf, and replace with Current Sales. Change the alignment of the label to be top left, and ‘un-bold’ the font. Add Current Sales to the Size shelf, then click on the Size shelf button, and change the size from Manual to Fixed, aligned left. This action will make the bars look like proper horizontal bars, with the same starting position, and a length proportionate to the value of Current Sales.

The manual vs fixed sizing option only becomes available when there is a measure (green pill) on both the Rows and the Columns, which is why we needed to create the Y-Axis Position field. Without this, we would only have had a slider size option which wouldn’t have achieved the desired result. The ‘height’ or depth of the bar is based around the position on the Y-Axis (ie 0.5) and the scale of the axis. Fixing the axis from -1 to 2 as mentioned at the start, positions the bar roughly central to where we want it and with a relatively narrow height. If you adjust the axes, you will see how this impacts the bar chart.

YoY Sales Column

For this column, we need to know what the previous year’s sales were, the % YoY difference and whether that difference was positve, negative or didn’t change

PY Sales

IF YEAR([Order Date]) = [Latest Year]-1 THEN [Sales] END

YoY Sales Diff

(SUM([Current Sales]) – SUM([PY Sales]) ) / SUM([PY Sales])

format this to a % with 0 dp

YoY Sales

SIGN(ROUND([YoY Sales Diff],2))

The SIGN() function is a more efficient way than saying IF value is >0 THEN… ELSEIF value < 0 THEN…. ELSE … END. SIGN() returns +1, 0 , -1 depending whether difference is positive, negative or the same.

We also need

YoY Sales Axis

MIN(0)

Add this field to the Columns shelf. Change the mark type to shape and add YoY Sales as a blue discrete pill to the Shape shelf. Use the arrow shapes and assign to the 1, 0 -1 values accordingly. The arrow shapes are available on the challenge page if they’re not already available for you (you might find them in the Arrows shape palette if that exists). Refer to this blog to understand how to add custom shapes.

Add YoY Sales as a discrete blue pill to the Colour shelf and adjust the colours using the blue, red and light grey options referenced in the requirements.

Add YoY Sales Diff to the Label shelf and align middle right. Unbold the text.

Profit Rank Column

Create new fields

Profit Rank Axis

MIN(0)

and

Current Profit

IF YEAR([Order Date]) = [Latest Year] THEN [Profit] END

formatted to $ with 0dp, and then create

Profit Rank

RANK(SUM([Current Profit]))

Add Profit Rank Axis to Columns and then add Profit Rank to the Label shelf. The mark type should already be set to a circle and coloured correctly to dark grey. Align the label middle centre (if it’s not already), and adjust the table calculation so it is computing by Sub-Category.

Current Profit Column

Create

Current Profit Axis

MIN(0)

Add to Columns and change the mark type to square. Add Current Profit to the Label shelf – align middle centre and un-bold. Add Current Profit to Colour. Edit the diverging colour legend. Click on the dark orange coloured square at the left side of the colour scale and change the colour to match the red hex code provided. Similarly click on the blue colour square at the right side and change the colour to match the blue provided. Tick the Use Full Colour Range option.

Create a new field

Current Profit – Size

MIN(1)

and add to the Size shelf, then increase the Size slider to as large as possible.

Change in Profit Columns

The positive and negatives values indicating the change in profit from the previous year is built as two separate columns. So we need

Change in Profit Neg Axis

MIN(0)

Change in Profit Pos Axis

MIN(0)

We need to determine the profit for the previous year

PY Profit

IF YEAR([Order Date]) = [Latest Year]-1 THEN [Profit] END

and with this we can work out the change

Change in Profit

SUM([Current Profit]) – SUM([PY Profit])

We then also need to explicit fields to plot on each axis.

Profit Change -ve

IF SIGN([Change in Profit]) = -1 THEN [Change in Profit] END

This field will only contain a value if the Change in Profit field is negative. Format this to $ with 0dp

Profit Change +ve

IF SIGN([Change in Profit]) = 1 THEN [Change in Profit] END

This field will only contain a value if the Change in Profit field is positive. Format this to $ with 0dp

Add Change in Profit Neg Axis to Columns and change the mark type to bar. Add Profit Change -ve to the Size shelf, and adjust the Size so it is Fixed and left aligned. Add Profit Change -ve to the Label shelf and align top right and un-bold. Add Change in Profit to the Colour shelf, and adjust the diverging colour legend exactly as described above.

Now repeat. Add Change in Profit Pos Axis to Columns and change the mark type to bar. Add Profit Change +ve to the Size shelf, and adjust the Size so it is Fixed and left aligned. Add Profit Change +ve to the Label shelf and align top right and un-bold. Add Change in Profit to the Colour shelf, and adjust the diverging colour legend exactly as described above.

Then right click on the Change in Profit Pos Axis and Add Reference Line. Create a Constant reference line of 0 value. This line won’t be that visible at this point, but once we remove all the other formatting, it will display.

Profit Ratio Column

Create the field

Profit Ratio Axis

MIN(0)

and

Current Profit Ratio

SUM([Current Profit])/SUM([Current Sales])

format this to % with 1 dp.

and

Profit Ratio – Colour

SIGN([Current Profit Ratio])

Add Profit Ratio Axis to Columns and change the mark type to Shape. Select the rounded shape from the shape palette (again this is provided on the requirements page and needs to be added as a custom shape).

Add Profit Ratio – Colour to the Colour shelf as a blue discrete pill and adjust accordingly to use the red and light grey options provided. Increase the Size of the shape as necessary, and then add Current Profit Ratio to the Label shelf.

Tidying up

So we’ve built the table, just need to clean it up by

• Right click the y-axis and uncheck show header
• Right click one of the x-axis and uncheck show header
• Right click on the Sub-Category label at the top of the column and select hide field labels for rows
• Format the chart and remove all column dividers
• Remove all grid lines, zero line, axis rulers and axis ticks.
• Select the All Marks card and click on the Tooltip button and uncheck show tooltips

The sheet can now be added to a dashboard. Use a horizonal container positioned above the chart and add text objects to create the column labels. My published viz is here.

Happy vizzin’!

Donna