For the final challenge of 2021, Luke set this challenge, to present a table containing both actual Sales values and % of Total Sales values using a single measure only.

The basic principal of this is explained within this Tableau KB article, although to get the exact display, will require a bit extra.

Building the table

Add Category & Sub-Category to Rows and Order Date to Columns. Change the Order Date so it is just displaying the month only. Add Sales to Text. Add Grand Totals and all Sub-Totals (Analysis > Totals menu). Set the totals to display at the top (Analysis > Totals menu again).

What we’re looking to do is change all the Total values in each Category section to be a % of Total instead, but just display the actual Sales value for all the other cells.

Custom Measure

IF [Size] = 1
THEN SUM(Sales)
ELSE SUM(Sales)/TOTAL(SUM(Sales))
END

Add this onto the Text shelf too and edit the table calculation so both the Custom Measure and Size nested calculations are computing by the Category field only. This should give you 0s in the Total fields for the Custom Measure value.

Remove Sales from the Text shelf, as we don’t need this now.

Format the Custom Measure

Click on the Custom Measure pill and select Format. The Format dialog should present on the left hand pane.

On the pane tab, set the default format to be $ 0dp, set the Totals format to be % 0 dp and set the Grand Totals format to be $ 0dp

Change the Total Labels

Right click on one one of the Categories (eg Furniture) and Format. On the left hand pane, change the Grand Total label to be Total.

The right click and format one of the Sub-Categories and change the Totals label to be % of Total.

Colouring the cells

Add Custom Measure to Colour (ensure the table calculation is set as described above) and change the mark type to square.

Edit the colour legend to use the green-blue-white diverging palette and set to include totals and full colour range. Set the centre of the range to be 0.

This doesn’t quite give us what we need, as the % of Totals aren’t coloured as we need.

The values for these although displaying as % are essentially the values between 0 and 1. But the ‘green’ will only display for values less than 0. So we can amend the Custom Measure to deal with this

Custom Measure

IF [Size] = 1
THEN SUM(Sales)
ELSE -SUM(Sales)/TOTAL(SUM(Sales))
END

We make the % of total to display as -ve, which now gives the colouring we need,

Finally, we need to ensure the -ve doesn’t display so we just need to re-custom format the Totals field of the Custom Measure to be 0%;0%

My published viz is here.

Happy vizzin’! Stay Safe!

Donna