For this week’s challenge, Yoshi got us creating gauge charts using Tableau’s radial viz extension.
Modelling the data
Yoshi provided a version of the Superstore dataset along with a Budget csv. After downloading, I related the 2 files within Desktop using the following relationships pictured below
Building the gauge chart
On a sheet, select the Add an extension option from the mark type dropdown and then select the Radial (by Tableau) option, and click Open on the resulting screen
Add Measure Names to Ring and Measure Values to Angle and then add Measure Names to filter and retain the Sales and Budget options only.
Click Format Extension and then set the options as follows
Total angle (degrees) : 180
Starting angle (degrees) : 270
Ring padding : 30
Segment padding : 5
Segment labels : Ring and Angle Values
Font : size 9
Centre size (%) : 50
Show centre label: on
Automatic font size: on
Edit colours and select appropriate colours
Adjust the order of the Sales and Budget pills in the Measure values pane if required
Add Region, Segment, Category and Order Date at the month-year level to the Filter shelf. Set Category to Furniture and Order Date to December 2025.
Format the Measure Values pill on the Angle shelf so that it displays the numbers as $ with 0 dp.
Create a new field
Acheivement %
(SUM([Sales])/SUM([Budget])) / 2
format this to % with 1 dp and add to the Centre shelf. Note, the division by 2 is necessary due to their being 2 measures (and therefore 2 marks) displayed and the number duplicating itself.
Create another field
Tooltip – Achievement %
(SUM([Sales])/SUM([Budget]))
Format this to % with 1 dp and add to the Tooltip shelf along with Sales, Budget and Category. Update the Tooltip to suit. Edit the title to reference the Category field, and then name this sheet Gauge – Furniture or similar.
Then duplicate the sheet. Change the Category filter to Office Supplies and name the sheet Gauge – Office. Repeat to create a version for Technology. So you should end up with 3 sheets, one for each Category. Apply the Region, Segment and Order Date filters to be shared across all 3 workbooks.
Building the bar chart
Create a field called
Sales Rank
RANK(SUM([Sales]))
and change it to be discrete (right click > convert to discrete)
Add Category to Columns and Sales Rank to Rows and Sub-Category to Detail. Set the table calculation associated to Sales Rank to be computing by Sub-Category only.
Double click into Columns and manually type MIN(1) to create a ‘fake axis’. Change the mark type to bar and edit the axis to be fixed from 0 to 1. Widen each row slightly. Then move Sub-Category to Label
Then via the Colour shelf, reduce the opacity to 0% and remove the border
Now add Sales to Columns. and on the Sales marks card, move Sub-Category back to Detail, and rest the opacity to 100%
Colour the bars as required. Add Budget to the Detail shelf, then add a reference line to the Sales axis, which shows the Budget per cell as a line with a fill below of light grey.
From one the gauge sheets, set the Region, Segment and Order Date filters to also apply to this sheet.
Add Achievement % to Tooltip and adjust the Tooltip on the Sales marks card only. Remove all the text from the Tooltip on the MIN(1) marks card.
Then format the sheet by
editing Sales axis and removing the axis title
editing the MIN(1)axis and removing the title and tickmarks
hide the sales Rank header (right click pill > uncheck show header
hide the Category header
remove row & column dividers
Then add the Gauge sheets into a horizontal container on a dashboard, setting the container to ‘distribute contents evenly’. Add the bar chart underneath, but I ‘floated’ it into position as each gauge chart object takes up more vertical space than necessary (the size of the object doesn’t adapt to the fact you’re only showing half a circle).
My inspiration for this week’s #WOW challenge came from Sarah Palette’s post on X from last year, and has been something I’d been meaning to try for ages, so figured a #WOW challenge was the perfect opportunity. Sarah has her own blog post which contains the core ‘trick’ to nailing the display, but as usual I’m going to walk you through step by step 🙂
After connecting to the data, start by adding Sub-Category to Rows, Sales to Columns, Category to Colour and sort by Sales descending. Adjust the colours to suit.
Format Sales to be $ to 0 dp, and add to Label. Apply a quick table calculation of Percentage of total to the Sales pill, and then format the pill to be % to 1 dp. Add another instance of Sales to Label. Adjust the layout and format the label to 8pt bold and match mark colour. Reduce the Size of the mark.
Double click into the Columns shelf, and manually type MIN(0). Then drag the MIN(0) pill from Columns and drop it on the Sales axis when the green ‘2-column’ icon appears. This automatically adds Measure Names and Measure Values into the view. Re-order the pills in the Measure Values section. This display now has 2 measures sharing a single axis.
Change the mark type to Line and line type to stepped (via the Path pill). Adjust the Label so it is labelling line ends and start of line only. Align label top right. Adjust the size of the line as required.
Click on the MIN(0) pill in the Measure Values section, and while holding down Ctrl drag the pill to Columns to create a copy of the same measure. This is important as typing in another instance of MIN(0) will create another separate measure, and we need it to be the same one.
This will create another marks card (MIN(0)). Remove the Sales pills from the Label shelf, and add Sub-Category instead. Align the label middle right, and add a couple of spaces before the Sub-Category text. Set the chart to be dual axis, synchronise the axis and then remove Measure Names from the All marks card. Adjust the width of each row to push the Sub-Category label into the corner of the L.
To give the labels at the end of the bar some ‘breathing room’, create a field
Max Sales + 10%
WINDOW_MAX(SUM([Sales])) * 1.1
Add this to the Detail shelf on the All marks card. Adjust the table calculation so it is computing by both Category and Sub-Category. Then right click on the bottom axis and Add Reference Line that uses the average of the Max Sales + 10% field for the Entire Table. Don’t display and lines/labels/tooltip
Finally tidy up by
Hiding both axis (right click -> uncheck show header)
Hiding both the Sub Category and Measure Names headers (right click, uncheck show header)
Remove all gridlines, zero lines, row & column dividers
Adjust Tooltip as required
Add a title colouring the text of the words to match the colours used in the legend.
It was Yoshi’s turn to set the challenge this week. The requirement was to build a waterfall chart, and I have to confess I did end up having to have a sneak peak at Yoshi’s solution to point me in the right direction.
I tend to always be looking for generic solutions, and in this case trying to make use of Measure Names / Measure Values, but struggled to do this. When I peaked at the solution, I found there was an element of ‘hardcoding’ being applied for the specific layout. Armed with that knowledge, I was then able to build a solution which ended up differing from Yoshi’s, but (looks to) produces the same outcome.
Defining the reporting period
The viz is driven by a Base Date input control that allows the user to select a date. Based on the date selected, the viz then displays information for the whole of the previous month, and compares that to the same month in the previous year. This means if the user selects any date from 01 Aug 2025 to 31 Aug 2025, the viz shows the information related to the whole of July 2025 and compares it to July 2024.
We will use a parameter to capture the date inputted by the user, but rather than ‘hardcode’ the data to use, I’m going to set it based on a field in the data set that I’ll create
Date Default
IIF(YEAR(TODAY())>2025, #2026-01-01#, TODAY())
The data set we’re using goes up to 31 Dec 2025. To ensure the viz still shows data if it’s accessed in 2026 or beyond, I’m going to set the date to 01 Jan 2026 if we’re looking at the viz in 2026 or later, otherwise I’ll default to whatever ‘today’ may be. This means that from 01 Jan 2026 onwards, the viz by default will always show the data for December 2025 compared to December 2024.
With this field, I can then create the parameter
pDate
Date parameter that references the Date Default field when the workbook is first opened
And with the date captured by the user, we can then determine the date of the previous month we’ll be reporting over
This truncates the pDate value to the 1st day of that month, then subtracts a month to return the 1st day of the previous month. Eg if pDate is 20 Aug 2025, it is first truncated to 1st Aug 2025, then 1 month is subtracted to return 1st July 2025.
We’ll then refer to this field when determining all the measures we need to build.
Calculating the measures
There are multiple measures we need to determine to build this viz – information for the previous month, the previous month last year and then the difference between the two. So what follows is just a list of all these 🙂
Sales – Last Month
IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Sales], NULL)
format this to $ with 0 dp.
Sales – Last Month LY
IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Sales], NULL)
format this to $ with 0 dp.
Sales – Last Month YoY
(SUM([Sales – Last Month]) – SUM([Sales – Last Month LY])) / SUM([Sales – Last Month LY])
custom format this to a % with 0 dp with an explicit + or – prefix : +0.0%;-0.0%;0.0%
Profit – Last Month
IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Profit], NULL)
format this to $ with 0 dp.
Profit – Last Month LY
IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Profit], NULL)
format this to $ with 0 dp.
Profit – Last Month YoY
(SUM([Profit – Last Month]) – SUM([Profit – Last Month LY])) / SUM([Profit – Last Month LY])
custom format this to a % with 0 dp with an explicit + or – prefix : +0.0%;-0.0%;0.0%
Profit Margin – Last Month
SUM([Profit – Last Month])/SUM([Sales – Last Month])
format to % with 1 dp
Profit Margin – Last Month LY
SUM([Profit – Last Month LY])/SUM([Sales – Last Month LY])
format to % with 1 dp
Profit Margin – Last Month YoY
[Profit Margin – Last Month] – [Profit Margin – Last Month LY]
custom format this to a % with 0 dp with an explicit + or – prefix : +0.0%;-0.0%;0.0%
SUM([Discount – Last Month]) – SUM([Discount – Last Month LY])
Cost
[Sales]-[Profit]
Cost – Last Month
IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Cost], NULL)
Cost – Last Month LY
IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Cost], NULL)
Cost – Last Month YoY
SUM([Cost – Last Month]) – SUM([Cost – Last Month LY])
There are additional fields we’ll need, but we’ll define these at the point we need them, as it’ll make more sense.
Building the KPIs
On a new sheet, double click into the Columns shelf and manually type MIN(0) to create a ‘fake axis’. Repeat this 2 more times, so 3 instances of MIN(0) exist and 3 marks cards exist. These are the placeholders for each of the KPIs we need to display.
On the 1st MIN(0) marks card, add Profit-Last Month and Profit – Last Month YoY to Label. Widen the row so you can see the text and change the mark type explicitly to Text. Adjust the label wording, layout and formatting as required, but don’t adjust the font colour. Instead create a new field
Colour – Profit
[Profit – Last Month YoY]>=0
and add this to the Colour shelf and adjust accordingly. Note – you will only ever get a true or false displayed and never both. You will need to adjust the date parameter to find a time period when the value is the opposite in order to set the opposite colour value.
Hide the Tooltip.
Repeat the process, adding Sales – Last Month and Sales – Last Month YoY to the 2nd MIN(0) marks card. Create
Colour – Sales
[Sales – Last Month YoY]>=0
and add to the Colour shelf.
The add Profit Margin – Last Month and Profit Margin – Last Month YoY to the 3rd MIN(0) marks card. Create
Colour – Profit Margin
[Profit Margin – Last Month YoY]>=0
and add to the Colour shelf. Hide column & row dividers, and name the sheet KPIs or similar.
Building the Waterfall
As mentioned above, this Waterfall chart involves a bit more “hardcoding” and the ‘explicit placement’ of the various measures into the viz.
We are displaying 5 different measures, each one in a ‘specific column’. We’re going to make use of the Row ID field to define what measure is displayed where and how it will be formatted.
Add Row ID to the Dimensions pane (drag above the line in the data pane, so it is above Measure Names). On a new sheet, add Row ID to Filter and filter to rows 1-5 only. Then add Row ID to Columns. Create a new field
Header
CASE [Row ID] WHEN 1 THEN ‘Profit (LY)’ WHEN 2 THEN ‘List Price Sales YoY’ WHEN 3 THEN ‘Total Discount’ WHEN 4 THEN ‘Total Cost YoY’ WHEN 5 THEN ‘Profit (TY)’ END
add this to Columns. Set the sheet to Fit Width.
This gives the start of the structure. We now want to display the relevant measure in each column, but we need a single field to do this, and the values of the fields need to be cumulative based on the preceding values.
Display Value
CASE [Row ID] WHEN 1 THEN {FIXED: SUM([Profit – Last Month LY])} WHEN 2 THEN {FIXED:SUM([Profit – Last Month LY])} + {FIXED:([List Price – Last Month YoY])} WHEN 3 THEN {FIXED:SUM([Profit – Last Month LY])} + {FIXED:([List Price – Last Month YoY])} + (-1*{FIXED:[Discount – Last Month YoY]}) WHEN 4 THEN {FIXED:SUM([Profit – Last Month LY])} + {FIXED:([List Price – Last Month YoY])} + (-1*{FIXED:[Discount – Last Month YoY]}) – {FIXED: [Cost – Last Month YoY]} WHEN 5 THEN {FIXED: SUM([Profit – Last Month])} END
Here, we’re using a FIXED LoD calculation to ensure the measures we need are calculating across the whole data set and not segmented by the Row ID which we’re just using as an arbitrary placeholder.
Add this to Rows and change the mark type to gantt bar.
The size of the gantt bar is determined by the specific measures (rather than the cumulative values)
Size
(CASE [Row ID] WHEN 1 THEN {FIXED: SUM([Profit – Last Month LY])} WHEN 2 THEN {FIXED:([List Price – Last Month YoY])} WHEN 3 THEN (-1*{FIXED:[Discount – Last Month YoY]}) WHEN 4 THEN -1*{FIXED: [Cost – Last Month YoY]} WHEN 5 THEN {FIXED: SUM([Profit – Last Month])} END) -1
Add this to Size
To label the bars create
Label
ABS([Size])
format this to $ with 0 dp. Add to Label and align centrally.
For the colouring create
Colour
IF ([Row ID]) = 1 THEN ‘Light’ ELSEIF ([Row ID]) = 5 THEN ‘Dark’ ELSEIF -1 * [Size] > 0 THEN ‘Blue’ ELSE ‘Red’ END
and add to Colour and adjust accordingly. And then create
Label Indicator
IF [Row ID] = 2 AND [Colour]= ‘Blue’ THEN ‘+’ ELSEIF (([Row ID] =3) OR ([Row ID]) = 4) THEN IF [Colour]=’Blue’ THEN ‘-‘ ELSE ‘+’ END END
Add to Label and adjust the font and layout of the label text accordingly.
Tidy up the formatting by
Adjust font style of the Header label.
Hide the Row ID pill (uncheck show header)
Hide the ‘header’ label (right click > hide field labels for columns)
Hide the axis title
Adjust the font style of the axis
Hide all axis lines/zero line, row & column dividers
Adjust the Tooltip
Name the sheet Waterfall or similar
Add the two sheets onto a dashboard and arrange with the parameter as required.
Yusuke set the #WOW2025 challenge this week, asking us to build a chart that was drill downable and drill uppable 🙂
I had a fair idea of how this was going to play out, knowing it would involve parameter actions and built the main table fairly quickly. Then it came to the parameter actions, and defining the logic to get them set to the right values. This was very tricky, and I confess I couldn’t completely manage it. The behaviour just wasn’t doing what I wanted 😦
So I looked at Yusuke’s solution, and even after using the exact same logic, field naming and parameter actions (including the names of these), it still wouldn’t quite do what Yusuke’s did. At the point the bars are expanded down to Manufacturer, if a Category is selected, Yusuke’s solution collapses back to the Category > Sub-Category level. Mine expands down to Manufacturer for the Sub-Category listed first (see below).
I spent a considerable amount of time trying to get this to work. Ultimately, I believe it’s something to do with the order in which the parameter actions get applied. In Yusuke’s solution, there are 4 parameter actions firing on each ‘click’, but they only affect 2 parameters. So one change it being applied before the other. But figuring out the order is tricky. From what I understand, actions of the same type (ie all parameter actions as opposed to filter actions, or set actions say), are applied based on alphabetical order. But, as I say, I tried naming my actions exactly like Yusuke’s (even copying and pasting from his solution), and I still couldn’t get his behaviour, and with all 4 actions applied, the drill-down from Sub-Category to Manufacturer didn’t work at all. I couldn’t get my actions to be displayed in the same order as Yusuke’s solution either, even by removing them and then adding them in the order listed, when I closed the dialog and re-opened, the order changed. So, as a result of this, my solution only has 3 actions and doesn’t quite behave exactly like Yusuke’s…. maybe I missed a tiny detail.. who knows, or maybe it’s just Tableau and some quirk in how things get applied…
Anyway, now I’ve said all that, let’s get on to the solution I did manage 🙂
Building out the calculations
For a challenge like this, I’m going to build out all my calculations into tabular form, so I can get the display and sorting as required, especially since table calculations are involved.
We need to capture the selections made ‘on click’ into parameters
pSelectedCategory
string parameter, defaulted to Furniture
pSelectedSubCat
string parameter, defaulted to Bookcases
The Sub-Category and Manufacturer to display will be based on the values in these parameters
On a sheet, add Category, Display – Sub Cat, and Display – Manufacturer to Rows and show the two parameters
If you change the values in the parameters, you’ll see how the display changes.
We want to get the total sales for each ‘level of the hierarchy’, so we can the compute the % sales, and apply sorting. We’ll used Fixed Level of Detail calculations for this.
Format all these to $ with 0 dp, add into the table and note how the values are duplicated across each row, depending on what ‘level of the hierarchy’ we’re looking at
Adjust the sort on the Category pill, to sort by Sales per Category descending – this will move the Technology row to the top.
Sort the Display – Sub Cat pill to sort by Sales per Sub-Category descending and sort the Display – Manufacturer pill to sort by Sales per Manufacturer descending.
With these fields, we can calculate the % of sales
% Sales per Category
SUM([Sales per Category]) / SUM({FIXED:SUM([Sales])})
% Sales per Sub-Category
SUM([Sales per Sub-Category]) / SUM([Sales per Category])
% Sales per Manufacturer
SUM([Sales per Manufacturer]) / SUM([Sales per Sub-Category])
format all these to % with 1 dp and add into the table
For the final display, we don’t want values in every row. We need values displayed at the first row of every level of the hierarchy. I’m going to use the INDEX() tableau calculation to help with this.
Index – Category
INDEX()
Index – Sub-Category
INDEX()
Make both of these fields discrete (right click > convert to discrete).
Add Index – Category to Rows to the right of the Category pill. Adjust the table calculation on the pill so it is computing using Display – Sub Cat and Display – Manufacturer only. This should index the rows so that the numbering restarts when the Category changes.
Add Index – Sub-Category to Rows to the right of the Display – Sub Cat pill. Adjust the table calculation on the pill so it is computing using Display – Manufacturer only. This should index the rows so that the numbering restarts when the Sub–Category changes.
We can then use this information to determine which rows need to display the % Sales values.
Display – % Sales per Category
IIF([Index – Category] = 1, [% Sales per Category],NULL)
Display – % Sales per Sub-Category
IIF([Index – Sub-Category] = 1 AND MIN([Category]) = [pSelectedCategory], [% Sales per Sub-Category],NULL)
Display – % Sales per Manufacturer
IIF(MIN([Sub-Category]) = [pSelectedSubCat], [% Sales per Manufacturer],NULL)
format all these to % with 1 dp, and add to the table (sense check that the table calculations for each field have the settings we applied to the Index fields above.
These 3 fields, are the core fields we need to use in the viz.
Building the Viz
On a new sheet, add Category, Display – Sub Cat, Display – Manufacturer to Rows and apply the sorting on each pill described above, and how the parameters.
Add Display – % Sales per Category to Columns and apply the table calculation settings described above. Add Display – % Sales per Sub-Category to Columns too, and again apply the table calc settings. Then add Display – % Sales per Manufacturer to Columns. Change the mark type on each of the 3 marks cards, specifically to use bar.
Add Category to the Colour shelf on the All marks card, and adjust accordingly.
On the Display – % Sales per Category marks card, add Category and Display – % Sales per Category to Label. Adjust the table calc settings of the % field as required. Adjust the layout of the label. Add Sales per Category to Tooltip and update the tooltip to suit.
On the Display – % Sales per Sub-Category marks card, add Display – Sub Cat and Display – % Sales per Sub-Category to Label. Adjust the table calc settings of the % field as required. Adjust the layout of the label. Add Sales per Sub-Category to Tooltip and update the tooltip to suit.
On the Display – % Sales per Manufacturer marks card, add Display – Manufacturer and Display – % Sales per Manufacturer to Label. Adjust the layout of the label. Add Sales per Manufacturer to Tooltip and update the tooltip to suit.
You may need to widen each row to see the labels displayed.
The axis titles on the top of the chart adjust based on the selections made. To present this within the chart itself (rather than using carefully positioned text fields on a dashboard), we need to make the chart dual axis using ‘fake axes’.
Double click into the space in Columns to the right of the last pill, and manually type MIN(0). Drag this field to sit between Display – % Sales per Sub-Category and Display – % Sales per Manufacturer.
Remove all pills from the MIN(0) marks card. Change the mark type to Shape and select a transparent shape for this (refer to this article to set this up – you can also use any other type of mark but set to very small, and 0% opacity on the colour to make it “invisible”, though a mark could appear on hover, which is why I prefer to use transparent shapes).
Click on the MIN(0) pill and set it to be dual axis, so 2nd column now has a MIN(0) axis heading.
Right click on this top axis, to Edit the axis – Change the Title to reference the pSelectedCategory parameter and set the tTck Marks to None
Repeat the process, creating another instance of MIN(0) to the right of the Display – % Sales by Manufacturer, but this time the axis title should reference the pSelectedSubCat field.
Tidy up the display formatting by
Add row banding with Band Size = 1and Level = 0, so the whole of the Furniture block is coloured grey.
Remove column dividers
Remove gridlines and zero lines
Hide the 3 pills on the Rows (right click each pill and uncheck show header).
Hide the null indicator (right click > hide)
Edit the bottom 3 axis to remove the titles and hide the tick marks on all
Make the axis heading section narrower
Add a border around each of the bars, and make each bar narrower if required
Add some space to the start of each bar, by adjusting the bottom axis to be fixed from -0.1 to 1
Update the title of the sheet, and name the sheet.
Adding the interactivity
Add the sheet to a dashboard.
Firstly, we’re going to stop the bars from being ‘highlighted’ when clicked. we’ll use the True/False filter action technique described here. Create 2 calculated fields True = TRUE and False = FALSE and add to the Detail shelf on the All marks card of the viz. Add a dashboard filter action
Deselect Marks
On select of the the Viz sheet on the dashboard, target the Viz sheet directly, setting True = False.
Now we need to deal with the parameter values. As I discussed at the start of this blog, getting the calculations required and making the functionality work was pretty tricky, so I’m just going to document what I’ve ended up using, that seems to mostly work. Note the names of the parameter actions which both affect the pSelectedCategory param are pre-fixed with a number to force the order (I did test with them the other way round, and things broke).
Create fields
Category for Param_1
IF (([pSelectedCategory]<> [Category]) OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=[Display – Sub Cat]) AND ISNULL([Display – Manufacturer])) THEN ‘ ! Please select a Category !’ ELSE [Category] END
Category for Param_2
[Category]
SubCat for Param
IF (([pSelectedCategory]<> [Category]) OR([pSelectedSubCat]=[Display – Sub Cat])) AND NOT(ISNULL([Display – Manufacturer])) THEN ‘ ! Please select a Sub-Category !’ ELSE [Display – Sub Cat] END
Then create 3 parameter actions
Set SubCategory
On select of the viz, set the pSelectedSubCat parameter passing in the value from the SubCat for Param field.
1. Set Category
On select of the viz, set the pSelectedCategory parameter passing in the value from the Category for Param_1 field.
2. Set Category
On select of the viz, set the pSelectedCategory parameter passing in the value from the Category for Param_2 field.
And fingers crossed, that should work, at least work the same as mine… my published viz is here. Note – when I uploaded to Tableau Public, the dynamic axes seemed to break, so I had to manually reset them on public…. or it may have broken before I published and didn’t realise.. the feature does seem to be a bit temperamental.
This week’s challenge once again has 3 levels, so I will blog the first solution and then build on/adapt from there. The main requirement is focused on working out how to rank the football teams based on multiple measures, and this is relevant for all the levels. Once again, we’re looking at data related to football teams in the English Premier league, and where they finished each season.
Defining the calculation to rank by
A team’s finishing position is based on a combination of points scored, goal difference and goals scored. But on rare occasions, a team is deducted points in the season, so we need to account for that.
Adjusted Points
SUM([Points]) – ZN(SUM([Points Deducted]))
Putting the key measures (Adjusted Points, Goal Difference and Goals For) per Team into a tabular view for just a single Season (eg 1993), we can see multiple instances of teams ending on the same number of Adjusted Points
Just ranking on this field, won’t necessarily order the teams in the correct order as Goal Difference and Goals For also need to be taken into consideration. We need to create a field we can rank based on a combination of all three fields, and to do this we’ll ‘weight’ each field by multiplying each field by a suitable proportion based on precedence. In the example shared via the community forum post the leading indicator was multiplied by 1000. In my case I’m going to divide.
Add this field into the table, and sort by it descending. You can see that for the 3 teams with 52 points, two of those teams have the same Goal Difference too, but are being correctly ordered based on the Goals For due to the result of the Adj Points | GD | GF calculation
Now we’ve got the core calculation nailed, we can start to build the viz.
Building the Beginner viz
On a new sheet, add Team to Rows and Season End Year to Columns.
We want to be more specific about the season, so create
Label Season
STR([Season End Year]-1) + ‘-‘ + RIGHT(STR([Season End Year]),2)
and add to Columns too.
To make the ‘squares’ rather than use the square mark type which can sometimes be a bit fiddly to get the size just right, I’m going to use a fake axis and a bar mark type.
Double click into Columns and type MIN(1.0) and change the Mark type to Bar. Adjust the Size to be as big as possible. Edit the axis to be fixed from 0-1.
Hide the Season End Year (right click the pill and uncheck Show Header). Rotate the Label Season (right click on the actual label and Rotate Label). Then make each column narrower and widen each row until you’re happy with the sizing.
We want to the Teams to remain listed in alphabetical order, but we want to know the position the team finished in the season, so we create
Rank Desc
RANK([Adj Points | GD | GF], ‘desc’)
Initially add this to Label, and then adjust the table calculation so it is explicitly computing by Team only.
You can verify the number associated to the teams in the 1993 season with the sorted table you made earlier.
But we’re not going to show the rank for every square, so move the Rank Desc from Label to Tooltip.
We do want to label just the team in first though so create
Is First
IF [Rank Desc] = 1 THEN ‘1st’ END
and add this to the Label shelf instead. Again adjust the table calculation as described above.
For the Tooltip we need to create
Tooltip Rank Suffix
IF [Rank Desc] = 1 OR [Rank Desc] = 21 THEN ‘st’ ELSEIF [Rank Desc] = 2 OR [Rank Desc] = 22 THEN ‘nd’ ELSEIF [Rank Desc] = 3 OR [Rank Desc] = 23 THEN ‘rd’ ELSE ‘th’ END
add this to the Tooltip shelf, adjust the table calculation as required. Also create
Tooltip – Note
IF ZN([Points Deducted])<>0 THEN ‘Additionally the team was deducted ‘ + STR([Points Deducted]) + ‘ points due to ‘ + [Notes] END
And then add Adjusted Points and Tooltip – Note to the Tooltip too. Adjust the Tooltip as required.
Finally adjust the look and feel by
Set the background colour of the worksheet to pale lilac (#f1eff6)
Set the mark colour to purple (#938bc1) and add a pale grey border
Set row and column dividers to a thin solid pale grey line (#e6e6e6)
Adjust the font colour of the row and column labels to black
Hide the Team row heading label (right click > hide field labels for rows).
Hide the Label Season column heading label ((right click > hide field labels for columns).
Hide the MIN(1.0) axis (right click pill – uncheck show header)
Make the mark label bold and centred
Set the sheet to fit width
Add a title
Name the sheet Beginner or similar
Building the Intermediate Viz
The requirement here, is we now need to highlight the teams that won and those that were relegated each season.
For this, we’re going to create another ranking field, but this time ranking in the other direction
Rand Asc
RANK([Adj Points | GD | GF], ‘asc’)
and then determine the team’s position in the season, giving consideration to the fact that in the 1994-95 season, 4 teams were relegated instead of the usual 3.
Position
IF [Rank Desc] = 1 THEN ‘C’ ELSEIF (MIN([Season End Year]) =1995 AND [Rank Asc] <=4) OR ([Rank Asc] <=3) THEN ‘R’ END
Duplicate the Beginner viz. Remove Is First from Label and add Position instead. Add Position to Colour and adjust accordingly.
Building the Advanced Viz
Now we want to extend the colouring, so the teams who were not the champions and who weren’t relegated, are coloured based on their league position.
For this duplicate the Intermediate sheet. Add Rank Desc to the Detail shelf, verifying the table calc is computing by Team only. Change it to be a discrete (blue) pill, and the change the pill so it is also on the Colour shelf, by updating the ‘detail’ icon to the left of the pill.
This will result in 2 pills on the Colour shelf and the Colour Legend listing all the permutations.
Adjust the colour legend, so all the R’s are red, the C is yellow, and the Nulls are set from automatically assigning the BuPu 3 Color Seq Palette. Use ctrl (windows) to select multiple entries in the colour legend, rather than assigning one by one.
Note if you don’t already have the BuPu 3 Color Seq Palette in your preferences file, you will need to add and then reload Tableau. For further information see here.
And that should complete all levels of the challenge. My published viz is here.
It was Yusuke’s turn for this week’s #WOW2025 challenge, posing a twist on a the creation of a highlight table.
Whenever I start a challenge, I take note of what’s going on – I interact with it, move my mouse around to see if there’s any clues. The main takeaway from this, is that I’d need a dual axis so I could have multiple marks cards to style differently – one to be coloured based on the Profit and one to be coloured based on whether the cell was selected or not. So we need to build a table using an axis.
Building the basic table
Add Order Date to Columns and then click the pill to expand the date hierarchy so Year and Quarter are displayed. Add Sub-Category to Rows.
Double click into Columns and manually type MIN(1.0) to create an axis. Change the mark type to bar, increase the size to as large as possible, and edit the axis to be fixed from 0 to 1. Add Profit to Colour and Profit to Label. Adjust the colour scheme as required (I used red-white-blue diverging and reduced the opacity to 70%). Adjust the label font to be grey text.
Widen each row; shrink each column and adjust the row and column dividers to be dashed grey lines. Update the font of the label headings. Adjust Tooltip to suit.
Storing the selected cells
To store the cells that have been selected, we’re going to use Sets. To build this set, click on a cell in the table, and then in the toolbar of the tooltip that displays, click the venn diagram symbol to create set
Name the set Cells Highlighted
Highlighting the selected cells
Each cell in the table we have built is a bar of length 1. We want to use a dual axis to create bars of length 1 only in the cells selected. So we need
Bar Length – Highlighted Cells
IF [Cells Highlighted] THEN 1 ELSE 0 END
We also only want the profit value to be displayed for these cells
Profit – Highlighted
IF [Cells Highlighted] THEN [Profit] END
Add Bar Length – Highlighted Cells to Columns to make a second axis, and a second marks card. Remove both existing Profit pills from this card. and instead change the Colour to black at 100% opacity, and add Profit – Highlighted to Label. Change colour of the Label text, and update the axis to be fixed from 0 to 1.
Make the chart dual axis and synchronise the axis.
Hide the axis (uncheck show header), and hide the Order Date label (right click -> hide field labels for columns)
Update the title of the sheet with the instructions and then add the sheet to a dashboard.
Adding the interactivity
On click, we want to add the cell (if unselected) to the set. For this we need a dashboard set action
Add to highlight
On select, target the Cells Highlighted set, adding values to the set on click, and keeping set values when cleared.
We also want to remove selected cells via a menu option, so create another dashboard set action
Remove from highlight
Display on the menu of the tooltip, and target the set Cells Highlighted by removing values from the set when the menu option is clicked, and keep values when selection cleared.
While these give us the functionality we need, it isn’t the best user experience – we have to click multiple times to get the display due to the ‘default’ behaviour of selected marks being automatically highlighted / non selected marks being faded out.
To resolve this, we need to utilise a couple of techniques blogged about here.
For the marks coloured by profit, we want to disable highlighting using a dashboard filter action and the true/false method.
Create calculated fields called
True
TRUE
False
FALSE
and add to the Detail shelf of the MIN(1.0) marks card only. Then on the dashboard add a dashboard filter action
Now if you click on an unhighlighted cell it should go black immediately. However, if you click on one of the black already highlighted cells, the other cells still fade.
Now we can’t apply the same method to that cell, as we then lose the hyperlink appearing on the tooltip on click. This is because the true/false method ultimately results in the cell being immediately deselected once selected, so the ‘click’ action, which results in the menu option showing, is cleared .
Instead we will use the dashboard highlight action technique also described in the blog.
Create a new field
Dummy
‘Dummy’
Add this to the Detail shelf of the All marks card (or add to both the MIN(1.0) and the Bar Length – Highlighted Cells marks cards). Then create a dashboard highlight action that just targets the Dummy field only, which as it exists in all cells, essentially selects them all.
Highlighting the word ‘colour’ in the title
I just did this by floating a blank object over the text which I set the background colour to black, and then set the object to move backwards. This did mean once I published to Tableau Public, I had to edit the viz online to ensure the object lined up to where I wanted.
This week’s #WOW2024 challenge was set by a guest poster, Robbin Vernooij, who wanted us to build a scatterplot with additional features to aid analysis. The main focus was on using Set Actions, so that’s what I used throughout the challenge, although parameters could be also be used.
Modelling the data
I took the simpler route when combining the data sources. After connecting to the Life Expectancy (lex.csv) data source, I deleted all the columns relating to the years except 2022 (Ctl Click to multi select the columns, and then right click and ‘hide’) . I then renamed the column from 2022 to Life Expectancy. The data source just contained 2 fields Country and Life Expectancy.
I then added the Co2 Pcap Cons.csv data source and related it via the Country field. Again I removed all the unnecessary year fields except the 2022 column, and renamed this to Co2 Pcap Cons.
Building the Scatter Plot
On a new sheet, add Co2 Pcap Cons to Columns and Life Expectancy to Rows. Add Country to Detail.
Hide the null indicator.
We need to identify a ‘selected’ country. We could use a parameter for this, but as mentioned above, I’ll use a set.
Selected Country Set
Right click on Country > Create > Set. Select a single country from the list (I chose Russia).
From this we need to determine the Life Expectancy and Co2 Pcap Con values for the selected country, but this value needs to be associated to every Country in the data set (ie every row of data), so we can use a FIXED LoD.
Selected Country Co2
{FIXED:SUM(IF [Selected Country Set] THEN [Co2 Pcap Cons] END)}
Selected Country Life Expectancy
{FIXED:SUM(IF [Selected Country Set] THEN [Life Expectancy] END)}
With these, we then want to define a min and max range for each measure so we can build the reference bands. The tolerance for this range wasn’t mentioned in the requirements, so I checked the solution to ensure I could validate other calculations later on.
Min Co2
[Selected Country Co2] – 1
Max Co2
[Selected Country Co2] + 1
Min Life Expectancy
[Selected Country Life Expectancy] – 4
Max Life Expectancy
[Selected Country Life Expectancy] + 4
Add all four fields to the Detail shelf.
Add a reference band to the Co2 Pcap Cons axis (right click axis > add reference line). Select band and set it to be from the Min Co2 field to the Mx Co2 field.
Repeat the steps for the Life Expectancy axis, to run from the Min Life Expectancy field to the Max Life Expectancy field.
In the example above, I have Russia as the selected country. We now want to identify all the countries that are falling within the bands.
[Life Expectancy]<= [Max Life Expectancy] AND [Life Expectancy] >= [Min Life Expectancy]
And with this, we create another set
Within Band Set
Select the Condition tab, and enter the formula
MIN([Within Co2 Band]) OR MIN([Within Life Expectancy Band])
Add Selected Country Set to Colour, to Size and to Shape. Adjust shape and size to suit. Then add Within Band Set to Detail and then adjust the icon to the left of the pill to the Colour icon, so 2 pills are now on the Colour shelf. Adjust the colours to suit.
Then create
Label – Country
IF [Selected Country Set] THEN [Country] END
And add to the Label shelf. Align bottom centre, and allow labels to overlap other marks.
Hide the Tooltip, Hide all the gridlines and row/column dividers. Format Co2 Pcap Cons and Life Expectancy to 1 dp. Name sheet Scatter or similar.
Building the Average Bar
On a new sheet, add Selected Country Set to Rows. Add Co2 Pcap Cons and Life Expectancy to Columns and change the aggregation of both from SUM to AVG. Manually reorder the In/Out header so Out is listed first. Show the labels. Add Selected Country Set to Colour on the All marks card and adjust accordingly.
Double click into Columns and type MIN(0.0), then move the pill so it’s the first one listed. Change the mark type of the MIN(0.0) marks card to shape. Add Selected Country Set to shape and adjust.
Create a new field
Header Label
IF [Selected Country Set] THEN [Country] ELSE ‘All others’ END
Add this to the Label shelf of the MIN(0.0) marks card. Align the label middle left.
Edit the MIN(0.0) axis to be fixed from -5 to 1 to shift the display to the right
Then remove the axis title, and set the tick marks to None so the axis for this section is hidden
Add Header Label to the Tooltip on the All marks card, and update the tooltip. Remove all gridlines, row & column dividers and hide the Selected Country Set pill on Rows (uncheck show header). Name the sheet Avg Bar or similar.
Building the Count Bar
On a new sheet, add Within Band Set to Columns and lex.csv(Count) to Rows. Add Within Band Set to Colour and Country to Detail. Adjust Colour and tooltip. Name the sheet Count Bar or similar.
Adding the interactivity
Add the sheets to a dashboard and arrange accordingly, Add a dashboard set action
Select Country
On hover of the Scatter chart, target the Selected Country Set. Only allow single selection. Assign values to the set on hover, and retain the values in the set when the selection is cleared.
And hopefully that should be it. My published viz is here.
For this week’s challenge, Kyle looked to solve a problem that he’s seen discussed within another blog – how to solve a highlighting problem when filtering donut charts.
I’ve been away on a little holiday abroad for a family wedding, so am on catch up this week. So I’m going to make this as brief as I can as time is limited.
Building the donut charts
Use the steps described in this blog post I wrote for my company to build a donut chart using the dual axis method.
For the Category donut chart, you will need Category on Colour and Sales on Angle of the outer Pie Chart. For the inner circle, you will need to add Sales to Text. Adjust the text as required. Sales needs to be formatted to $ with 0 dp.
For the Sub-Category donut chart, you will need to add Category to Colour. Then add Sub-Category to Detail and click on the 3 dots to the left of the Sub-Category pill and change to also add to Colour.
To adjust the colours, edit the colour legend, select all the options within the same Category. Select a sequential colour palette that matches the core colour for the category, then select Assign Palette. The colours should change to a range of that colour.
Create a new field
# Products
COUNTD([Product ID])
and add this to the Angle shelf. Add Sales to the Tooltip shelf and adjust the tooltip.
For the inner circle, add #Products to Text. Adjust the text as required
Filtering the donut
Add the two sheets to a dashboard. Add a dashboard filter action
Filter Cat
On select of the Category donut, target the Sub-Category donut chart passing in all fields. Keep filtered values when selection cleared.
Stopping the Category donut from being highlighted
Create new fields
True
TRUE
False
FALSE
and add these to the Detail shelf on the All Marks card of the Category donut sheet.
Then create a dashboard filter action
Unhighlight
On select of the Category donut on the dashboard, target the Category donut sheet itself, passing in the fields Tue = False. Show all values when selection cleared.
Now when the Category donut is clicked on, the other segments won’t fade. However, the selection is still visible – the edges of the pie are displayed.
Stop showing the selected section of the pie
For this we employ a trick mentioned in the blog post referenced in the challenge. Create a new field
Dummy
‘Dummy’
and add this the Detail shelf of a new sheet. Change the mark type to polygon so nothing is visible.
Add this to the dashboard as a floating object – make it small and place somewhere inconspicuous
Whilst the selections will still be visible when testing on Desktop, once published to Tableau Public, the presence of the polygon forces the whole dashboard to be rendered server side rather than client side. This reduces the amount of interactivity, and consequently the pie chart segments don’t display when clicked.
My #WOW2024 challenge this week, was to recreate this bar chart which displayed the total value in conjunction with the values split by another dimension, in this instance year. It was inspired by Sam Parsons’film franchises viz.
The viz is built on a single sheet, and only uses 3 fields from the data set, the Order Date the Sub-Category and the Sales value.
Let’s build…
On a new sheet, add Sub-Category to Rows and Sales to Columns and sort by Sales descending. Add Order Date to Colour to build a basic stacked bar chart.
We need to be able to show two bars directly ‘side by side’, but with different properties, so we need to end up with 2 marks cards. The ‘heights’ of the bars need to differ, so we’re going to create a ‘fake axis’ on Rows to help.
Double click into the Rows shelf and manually type MIN(0.5)
Change the mark type to bar. Each bar is now positioned based on it’s specific value (ie not stacked).
To get the bars to be positioned where we want them, apply a Running Total Quick Table Calculation to the Sales pill in the Columns shelf. Then edit the table calculation so that is computes by Year of Order Date only.
Add Sales to the Size shelf, and edit the size to be Fixed and aligned Right. You should now have a stacked bar effect.
Add Sales to Tooltip, then add a Percentage of Total quick table calculation. Edit the calculation so it is computing by Year of Order Date.
Adjust the Tooltip to reference the relevant details (note in the above gif, and my original published version, at some point the labelling went awry and the label was showing the cumulative sales value, and not the value for that year).
So now we’ve managed to build a segmented bar chart in a ‘different way’ by using an axis and the size field. We build on this to get the total bar. As this bar needs to be wider than the segmented bar, double click into the Rows shelf and type MIN(1.5), then from the MIN(1.5) marks card, remove the Order Date field from Colour. We now have 2 bars – 1 segmented and 1 not.
Make the chart dual axis and synchronise the axis, then right click on the right hand axis and move marks to back.
On the Min(0.5) marks card, move Order Date from Colour to Detail. Then on the All marks card, click on the Colour shelf and adjust the colour to suit (I set to #dec7b6) and add a white border.
Adjust the Tooltips on both marks cards so the MIN(1.5) is just showing the Sub-Category and Sales value, while the MIN(0.5) card is showing the Order Date year and % of sales value as well.
Narrow the width of the rows. Hide the right hand and the bottom axis (uncheck show header).
We want to make each pair of bars more distinctive, by providing more space between them. Edit the axis to fix the axis from -0.3 to 1.5.
Now hide the axis, and the Sub-Category column heading (right click label and hide field label for rows). Remove all row/column divider lines, zero lines and gridlines. Set the worksheet background colour (I used #fefaf1). Adjust the colour/style of the Sub-Category labels (I used bold, brown) and align top right.
And ta-dah! this should be your finished viz. My published version is here.
We’re going to building on this in a later challenge (I felt putting it all together in a single challenge might be a bit much), so look out for Part 2 in a few weeks time.
Lorna set a table calculation filled challenge this week to recreate some KPI cards using an aggregated and amended version of the Superstore data set.
Lorna purposefully used a string Period field to define the timeframe to encourage the use of table calculations. No doubt, there are ways you could use string functions/regex to extract the relevant year and month number to come up with a different solution, but I’m going to head down the intended route.
And as with any table calculation based challenge, I find it best to always build out all the calculations I need into a tabular display to start with before building the viz. So let’s get started…
Building out the Calculations
We need to have a handle on more of the data than just that associated to the Period we’re interested in, so we can’t use a simple Quick Filter on the Period field to restrict the data, otherwise we can’t ‘access’ data for the previous period. So to manage which Period we want to focus on, I created a parameter
pSelectedPeriod
string field that uses a list where the values are added from the Period field. Default to P12 Y2022/23
On a new sheet, add Period to Rows and then add the Sales , Profit and Quantity measures to Text. Show the pSelectedPeriod parameter
What we’re going to do is get the values for each measure that is associated to the pSelectedPeriod, and display that value over every row.
Curr Sales
WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Sales] END))
If the Period matches that in the parameter, then get the Sales value and then use Window_MAX to ‘spread’ that value over every row
Add this to the table, and edit the table calculation to ensure the value is being computed by Period.
Repeat the same for Profit and Quantity
Curr Profit
WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Profit] END))
Curr Qty
WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Quantity] END))
If you change the parameter, you should see all the values in the last 3 columns changing to reflect the value from the first 3 columns of the relevant row.
Now we need to get the value from the previous period, ie the data from the previous row
Prev Sales
WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Sales]),-1) END)
The LOOKUP function is taking the Sales value from the previous 1 row (-1), and then WINDOW_MAX is once again ‘spreading’ this value across every row. We also need
Prev Profit
WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Profit]),-1) END)
Prev Qty
WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Quantity]),-1) END)
Add these to the table, and again adjust the table calc settings for every field to compute by Period.
Now we have the current and previous values for each measure, we can work out the % difference
Diff Sales %
([Curr Sales]-[Prev Sales])/[Prev Sales]
Format this to a % with 1 dp
Diff Profit %
([Curr Profit]-[Prev Profit])/[Prev Profit]
Diff Qty %
([Curr Qty]-[Prev Qty])/[Prev Qty]
Add all these onto the sheet, and remember the table calc settings (for these, there are nested table calcs, so make sure both are set properly).
We now need an arrow indicator to display up or down depending on the % value, and this needs to display as a different colour, so we need two fields per measure.
Add these to the sheet if you wish too (apply the table calc settings), but you’ll only get a value for one or the other field depending on whether the difference was +ve or -ve. Below I’ve just added the two Sales indicators
The Tooltip displays the value of the two Periods being compared. One of these is in the parameter, but we need to capture the other
Previous Period
WINDOW_MAX(IF [pSelectedPeriod] = MIN([Period]) THEN LOOKUP(MIN([Period]),-1) END)
So now we have all the values we need for the KPIs captured against every row in the dataset. So now we want to just show a single row. It could be the first, it could be the last… based on Lorna’s hint, let’s filter to just show the row related to the pSelectedPeriod value.
Filter Selected Period
[pSelectedPeriod] = LOOKUP(MIN([Period]),0)
Using the offset of 0 with the LOOKUP, returns the value for the row you’re on, so adding this to the FIlter shelf and selecting True, filters the display to the row where the Period matches the parameter. NOTE if you adjust the table calc settings of this field after adding to the filter shelf, you’ll need to reselect the option to filter to True.
As this is a table calculation, the ‘filter’ is applied later in the order of operations, so information about the other rows in the table can be referenced. Filtering just by Period as a quick filter, is essentially a dimension filter and that happens earlier on in the process, meaning the data about the other rows would be inaccessible.
So we have all the fields, now build the cards.
Building the KPI Cards
On a new sheet, double click into Columns and type in MIN(0). Repeat this 2 more times. This gives us 3 axis to build each of the 3 cards.
On the All marks card, add Period to the Detail shelf. Show the pSelectedPeriod parameter. Add the Filter Selected Period to the Filter shelf and set to true (adjusting the table calc and resetting the filter value as required).
Change the mark type of the All marks card to shape and select a transparent shape (see here for more details).
On the first MIN(0) marks card, add Curr Sales, Prev Sales, Diff Sales %, Diff Sales Indicator +ve and Diff Sales Indicator -ve to the Label shelf. Remember to apply the table calc settings for all of the fields!
Adjust the text within the label, so it is formatted and positioned as required and then align middle centre.
On the middle MIN(0) marks card, do similar by adding the equivalent profit fields onto the Label shelf, and then repeat again for the bottom MIN(0) marks card, adding the quantity fields to the Label shelf.
On the All marks card, add Previous Period, Curr Sales, Curr Profit, Curr Qty Prev Sales, Prev Profit, Prov Qty, Diff Sales %, Diff Profit % and Diff Qty % to the Tooltip shelf (remember to set those table calc settings!) Then adjust the Tooltip to display the text as required. I used the ruler to shift the starting position, along with tabs (the tab keyboard button) to ‘try’ to get everything to align, and it works for most circumstances….
Finally format the KPI card
Set the worksheet background colour to light grey
Remove all gridlines, zero line, axis lines
Set the column divider to be a thick white line
Set the row divider to be a thick white line
Hide the axis
Add the sheet onto a dashboard, and you should be done.
Donna + DataViz 