Can you build a satellite chart to represent values exceeding 100%

Donna ColesLeave a comment

For this week’s challenge, we’re using the data from a previous challenge and visualising it using Sam Parson’s ‘satellite’ chart idea – see here.

Modelling the data

Connect to the Food Self-Sufficiency csv file then add another connection to the Circle_Scaffold excel file. Relate the two together using a relationship calculation where 1 = 1 (ie relate every row in left hand data source to every row in the right hand).

Since we only care about data from FY2019, add a data source filter to set Fiscal Year = FY2019.

This saves us from having to apply that filter to each sheet we build.

Building a single spiral

On a new sheet, add Prefecture to the Filter shelf and select Akita-ken (which is near the top of the list and has a % value over 200%).

Create a parameter

pMinRadius

integer parameter defaulted to 500

To build the spiral, we need to plot a mark for every percentage point from 0 up to the Food Self-Sufficiency % value. For this we will need to determine an (x,y) coordinate value for each point, which will require some trigonometry, based on the diagram below

For each point on the circle, we will need to identify the x & y position of where the radius intersects the edge of the circle. As we are building a spiral, the radius of the circle will increase as we move around each percentage point, so we need

Radius

[pMinRadius]+[Path Percent Point]

We then need to determine the angle θ. As a circle is 360°, and a complete circle represents 100%, then 1% is 360/100, so the angle (in radians) for each % point plotted round the circle can be calculated as

Angle

RADIANS([Path Percent Point] * (360/100))

X

(SIN([Angle]) * [Radius])/360

Y

(COS([Angle]) * [Radius])/360

Now create the point

Spiral Point

MAKEPOINT([X],[Y])

Note – the X & Y values are divided by 360 due to the spiral we’re building and the increasing radius when displayed using map layers. If we were just plotting X against Y and not using map layers, this wouldn’t be required.

Double click on Spiral Point to automatically add Longitude and Latitude fields to the sheet.

Change the mark type to line and then add Path Percent Point to the Path shelf.

Add Prefecture to the Detail shelf, as it’ll be needed later when we build the trellis and remove the filter.

At this point, the spiral is showing 3 complete revolutions, as the data in the circle_scaffold data set contains info for up to 300%. We need to restrict it so we only show up to the Self-sufficiency ratio… so we need

Filter Percent Point Displayed

[Path Percent Point] <=[Self-sufficiency ratio for food in calorie base 【%】]

Add this to Filter shelf and set to True.

We now want to colour the spiral based on the percentage point associated to each mark plotted being <100%, between 100% & 199% or >= 200%, so we can use

Colour – Spiral

FLOOR([Path Percent Point]/100)

which will return an integer of 0, 1, or 2

Change this field to be discrete and then add it to the colour shelf and adjust colours accordingly.

Now obviously, you might be thinking things aren’t quite right – we’re not starting at the top and rotating differently. Simply pressing the swap rows and columns icon in the menu bar will resolve this, but if we do that too early, we lose the ability to add map layers, so leave as is for now.

Add the label map layer

Create a 0 point

Zero

MAKEPOINT(0,0)

Drag this onto the canvas and drop when the Add a Marks Layer option appears

This has the effect of creating a 2nd marks card

and now we have this, we can press the swap rows and columns icon in the menu bar to get the start of the spiral at X=0

Change the mark type to circle and add Self-sufficiency ratio… and Prefecture to the Label shelf. Adjust the font style and align centrally. Set the colour of the circle to white and increase the size. Move the Zero marks card to be below the Spiral Point marks card.

Rename the marks cards if you wish.

Add the starting point map layer

We need to create a point for the start of each line which is at the 0% mark

Start Point

MAKEPOINT((IF [Path Percent Point] = 0 THEN [X] END), (IF [Path Percent Point] = 0 THEN [Y] END))

Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Colour the mark to the same base colour you used for your <100% range and remove the border. Rename the marks card to 3.Start Point

Add the end point map layers

Create a new point to represent the end of each line

End Point

MAKEPOINT((IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [X] END), (IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [Y] END))

Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Add Colour-Spiral to the Colour shelf as a discrete dimension (blue disaggregated pill) and remove the border. Rename the marks card to 4.End Point Outer.

Add another instance of End Point as another marks layer. Again change the mark type to circle and adjust the size so it is smaller than the previous circle, and set the Colour to white. Rename the marks card to 5.End Point Inner.

Tidy up by

  • removing the Tooltip from each layer
  • disabling selection of each map layer (so nothing happens when you hover over it)
  • Hide the axis
  • Remove axis rulers and gridlines, but make sure the zero lines are shown
  • Hide the null indicator

Name the sheet Single Spiral or similar.

Building the trellis

Duplicate the single spiral (so if things go awry, you can get back to this). Then start by adding Prefecture to the Detail sheet of all the marks card.

When creating trellis charts, we need to create fields that represent which row and which column each Prefecture should sit in. There’s lots of blogs on creating trellis charts. As we know the number of rows and columns we need, I created

Cols

INT((INDEX()-1)%10)

Rows

INT((INDEX()-1)/10)

and make both fields discrete.

Add Cols to Columns and Rows to Rows. Adjust the table calculation setting of each field so it is computing by Prefecture only, and custom sorted descending by Self-sufficiency ratio…

Then show the Prefecture filter and select all values to display the ordered set of Prefectures.

Hide the Rows and Cols fields, remove row & column dividers. Adjust the size of the start and end point circles to suit, and if the zero lines aren’t showing, reduce the size of the label circle map layer and fit to Entire View.

Then name this sheet Trellis or similar and add to the dashboard.

My published viz is here

Happy vizzin’!

Donna

Can you build a ranked heatmap tile?

Donna ColesLeave a comment

Erica had a guest coach, Valerija Kirjackaja setting the challenge this week, asking us to use table calculations to build this heatmap table.

I built an initial version of the heatmap only, which I’ve published here, but I couldn’t get the display to match Valerija’s when I clicked on it. (Note – I wasn’t bothered about Viz in Tooltip part at this point). I used 2 dimensions to display the yellow header section, and you can see below, when clicking, this is noticeable. But Valerija’s solution treats the header as a single entity.

I couldn’t figure out how she’d managed this, so I had to have a look at the solution, and then I built my own instance, which is what I’ll now blog about.

Defining the core fields

Connect to the datasource and add a data source filter to restrict the data by the latest year (as I used Superstore 2025, I filtered to 2025). Doing this meant I didn’t have to worry about adding worksheet level filter fields.

On a new sheet add Category and Sub-Category to Rows and add Sales to Text and then sort descending so you can see the data we need to work with. Format Sales to be $ to 0 dp.

Create a new field

Sales by Cat Rank

RANK(SUM([Sales]))

change it to be discrete and then add to Rows. Adjust the setting of the table calculation so it is computing by Sub-Category only, so the ranking restarts for each Category

We will also need to display the Category in upper case, so create

Category Upper

UPPER([Category])

and add to Rows.

Having this tabular layout just lets us clarify how the table calculation will be working.

Building the Heatmap Table

On a new sheet, add Category to Columns and Sub-Category and Sales to Text and align centrally (Note – I originally put Category Upper on the columns, and then changed later after taking all the screen shots)

Add Sales by Cat Rank to Rows. Change it to a continuous (green) pill and edit the axis to be reversed. Adjust the table calculation so it is computing by Sub-Category only.

Create a new field

One

1

Change the mark type to gantt bar and add One to the Size shelf, setting the aggregation to Avg. Increase the size of the mark via the Size shelf to as large as possible. Add Sales to Colour then add a white border.

We need the axis numbers to be central to the ‘row’, so to fix this, double click into the Sales by Cat Rank pill and change it to subtract 0.5 ( [Sales by Cat Rank] -0.5 )

Re-edit the axis to reverse it again.

So our ‘table’ is now displaying data on a axis from 0.5 up to 9.5. To add the header, we’re going to plot another mark at -0.5, so we can display a section of the same size as the existing ones. For this, double click into Rows and type MIN(-0.5).

This has the effect of creating a second marks card

Remove Sales from Colour and Sub-Category from Label. Add Category Upper to Label. Adjust the label text alignment and formatting and change the Colour to yellow.

Make the chart dual axis and synchronise the axis and we now have the required display.

Tidy up by

  • Remove row & column dividers
  • Remove gridlines, zero lines & axis ticks
  • Hide the right hand axis (right click > uncheck show header)
  • Hide the Category Upper column labels (right click pill > uncheck show header)
  • Remove the left hand axis title
  • Fix the left hand axis from -0.5 to 9.5
  • Format the axis with a custom formatting of #,##0;-#,##0;TOTAL so that 0 is displayed as the word TOTAL (this is very sneaky by the way, and took me a while to work out)
  • Then format the font to be bold
  • Adjust the Tooltip on the Sales by Cat Rank marks card to display the Sub-Category and Sales value.
  • Delete the text from the Tooltip on the MIN(-0.5) marks card
  • Name the sheet Table or similar

Building the Viz in Tooltip

On a new sheet add Order Date to Columns at the discrete month level (blue pill) and add Sales to Rows. Add Sub-Category and Category to Detail. Format the date axis so the dates are just using First Letter.

Create a new parameter

pSubCat

string parameter defaulted to Bookcases

Then create a field

Is Selected SubCat

[Sub-Category] = [pSubCat]

and add to the Colour shelf. Adjust accordingly, then make sure True is listed first in the legend, so the line is ‘on top’

Create a new field

Label Line

IF [Is Selected SubCat] THEN [Sub-Category] END

and add to the Label shelf and update to allow marks to overlap. Hide the Order Date label heading (right click > hide field labels for columns). Title the chart Line or similar

Remove gridlines and row/column dividers

Go back to the Table sheet, and update the Tooltip of the Sales by Cat Rank marks card to reference the Line chart and update the filter to pass as just <Category>

Adding the final interactivity

Create a dashboard and add the Table sheet. Then add a parameter action

Set Sub Cat Param

On hover of the Table sheet, set the pSubCat parameter with the value from the Sub-Category field, setting the value to <empty string> when the selection is cleared.

If all has been applied, then the line chart should just display the lines associated to the Sub-Categories in the same Category

My published viz is here.

Happy vizzin’!

Donna

Dynamic Dimension Colouring

Donna ColesLeave a comment

For this week’s challenge, Yusuke asked us to provide a solution to allow charts to be coloured by different dimension, but he sprinkled a few extras in just for good measure 🙂

Defining the parameter

The key driver here is going to be the use of a parameter to define the dimension we need to colour by.

pColourBy

string parameter defaulted to Order Date, listing the 4 options as below

We then need a field that uses this parameter to define the actual dimension we’ll colour by

Colour

CASE [pColourBy]
WHEN ‘Order Date’ THEN STR(YEAR([Order Date]))
WHEN ‘Region’ THEN [Region]
WHEN ‘Category’ THEN [Category]
WHEN ‘Segment’ THEN [Segment]
END

Building the Order Date chart

On a new sheet, add Order Date to Columns and Sales to Rows. Change the mark type to Bar and add Colour to the Colour shelf. Adjust the colours to suit, set the opacity to 70% and add a white border. Show the pColourBy parameter.

Change the options in the pColourBy parameter and each time readjust the colours as you wish.

Add a reference line to the Sales axis that displays the value of Total Sales per cell

Format the reference line to format the displayed number in $M and bold font, and align top middle.

Create a new field

% of Sales

IF SUM([Sales]) / TOTAL(SUM([Sales])) <> 1 THEN SUM([Sales]) / TOTAL(SUM([Sales])) END

and format to % to 1dp. This will only display a value if its not 100%.

Add this to the Label. Adjust the table calculation setting so it is computing by the Colour field only.

Adjust the Label so the font is bold and the label only appears when Highlighted. Then update the Tooltip as required.

Although not explicitly called out in the requirements, I noted that if Yusuke clicked on the chart title, it reset the dimension to colour by. To deal with this we need to create

param Order Date

‘Order Date’

Add this to the Detail shelf.

We also need to ‘fake’ the title to be part of the chart itself (so it’s clickable). Double click into the Columns and manually type ‘Sales by Order Date’ and position the pill created before Order Date.

Right click on the column label (the text in darker font) and hide field labels for columns. Then right click on the column label to format – set the font to 12pt and bold, align left and shade the background to light grey. Increase the width of the column heading.

Then right click on the corner whitespace next to the heading just created, and format. Apply a light grey shading to the corner too.

If the ‘title’ is clicked, we don’t want it to be ‘highlighted’/’selected’. For this we will need fields

True

TRUE

False

FALSE

Add both of these to the Detail shelf.

Finally tidy up by removing the axis title, adjusting the font of the axis labels (I made them a bit darker), and removing row & column dividers. Name the sheet Order Date or similar.

Building the Region chart

Duplicate the Order Date chart and then click the option in the menu to swap axis so we have a horizontal bar chart.

Move the ‘Sales by Order Date’ pill from Rows to Columns and update the text to become ‘Sales by Region’ instead. Drag the Region pill and drop it directly over the Order Date pill on the Rows so it replaces it and all references to the field are replaced too. Widen the rows.

Right click on the ‘Region’ text in the column heading and hide field labels for rows. Format the reference line to align middle right.

Create a new field

param Region

‘Region’

and add this to the Detail shelf instead of the param Order Date field. Name the sheet Region or similar

Building the Category Chart

Duplicate the Region chart, and go through similar steps described above so the ‘title’ is Sales by Category and a new field

param Category

‘Category’

replaces param Region on the Detail shelf.

Building the Segment Chart

Repeat as above, this time setting the ‘title’ to Sales by Segment and a new field

param Segment

‘Segment’

replaces param Region on the Detail shelf.

Adding the interactivity

Add the sheets to a dashboard using layout containers and padding to organise as required. Then create the following dashboard actions

Highlight Action : Highlight

On hover of any of the charts on the dashboard, target all other charts, highlighting based on the Colour field only.

This action makes all the % labels appear when the mouse cursor is moved over the bars.

Parameter Action : Set Order Date Param

On Select of the Order Date sheet, set the pColourBy parameter with the value from the param Order Date field.

Parameter Action : Set Region Param

On Select of the Region sheet, set the pColourBy parameter with the value from the param Region field.

Parameter Action : Set Category Param

On Select of the Category sheet, set the pColourBy parameter with the value from the param Category field.

Parameter Action : Set Segment Param

On Select of the Segment sheet, set the pColourBy parameter with the value from the param Segment field.

These actions change the value displayed in the pColourBy parameter when the ‘title’ of the charts is clicked on.

Filter Action: Deselect Order Date Title

On select of the Order Date sheet on the dashboard, target the Order Date worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.

Filter Action: Deselect Region Title

On select of the Region sheet on the dashboard, target the Region worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.

Filter Action: Deselect Category Title

On select of the Category sheet on the dashboard, target the Category worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.

Filter Action: Deselect Segment Title

On select of the Segment sheet on the dashboard, target the Segment worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.

And once these have all been applied, you should have a functioning dashboard. My published version is here.

Happy vizzin’!

Donna

Let’s make a Tableau Pulse-Inspired Dashboard!

Donna ColesLeave a comment

Yoshi set the challenge this week to build a dashboard which looks like the visual you might get as part of a Pulse metric.

Define the parameters

Create a parameter to define the ‘reporting’ date

pBaseDate

date parameter defaulted to 21 Sept 2025

Create a parameter to capture the number of previous weeks the ‘forecasting’ values should be calculated against

pWeeks

integer parameter defaulted to 8, with a min value of 5 and max of 15, incremented every 1 step

Building the KPI card

This section displays the month to date sales and comparisons to previous month, based on the value in the pBaseDate parameter. We need several calculations

MtD Sales

IF [Order Date] >= DATETRUNC(‘month’, [pBaseDate]) AND [Order Date]<=[pBaseDate] THEN [Sales] END

format to $ with 0 dp.

Prev MtD Sales

IF [Order Date] >= DATEADD(‘month’, -1, DATETRUNC(‘month’, [pBaseDate])) AND
[Order Date]<= DATEADD(‘month’, -1, [pBaseDate]) THEN [Sales] END

MtD Sales Diff

SUM([MtD Sales]) – SUM([Prev MtD Sales])

custom format to +”$”#,##0;-“$”#,##0

MtD Sales % Diff

[MtD Sales Diff] / SUM([Prev MtD Sales])

custom format to +0.0%;-0.0%;0%

On a new sheet add MtD Sales, MtD Sales Diff and MtD Sales % Diff to Text. Change the mark type to shape and set to be a transparent shape (refer to this blog to understand how to set this up). Adjust the layout and style of the text on the Label. Set the sheet to Entire View and align the text to the left. Update the title of the sheet and remove the Tooltip.

Building the Line Chart

Before building the viz, we’ll start by building the calculations and checking them through a tabular display.

On a new sheet, show the pBaseDate and pWeeks parameters. Then add Order Date as a discrete exact date (blue pill) to Rows and add MtD Sales to Text. The MtD Sales values should only display against the dates from 1st to 21st Sept.

For each day, we need to calculate the 25th percentile of the Sales value using the gp model against the same day of the week for the previous data.

25th Percentile

MODEL_QUANTILE(“model=gp”,0.25,SUM([Sales]),ATTR([Order Date]),ATTR(STR(DATEPART(‘weekday’,[Order Date]))))

and we also need the 75th percentile

75th Percentile

MODEL_QUANTILE(“model=gp”,0.75,SUM([Sales]),ATTR([Order Date]),ATTR(STR(DATEPART(‘weekday’,[Order Date]))))

These 2 values give the ‘range’ we want to check the actual MtD Sales value against. Add both values into the table, and update the table calculation setting of each so they are computing explicitly by Order Date

However we only want the calculations to be based on the last x weeks, so we want to filter the display.

Dates to Include

[Order Date]>= DATEADD(‘week’, -1 * [pWeeks], [pBaseDate]) AND [Order Date] <= [pBaseDate]

Add this to the Filter shelf and set to True. This will adjust the 25th & 75th Percentile values as they are only considering data within the display (eg compare the highlighted values for 01 Sept against those in the image above)

But when it comes to ‘plotting this data’ on a chart, we only want to display the data for the current month (based on pBaseDate), so we need to apply a second filter that restricts the dates displayed further, but that does not eliminate the data in such a way that we lose reference to the previous x weeks.

We can do this using a filter based on a table calculation

Filter: Dates for Chart

LOOKUP(MIN([Order Date]),0) >=DATETRUNC(‘month’, [pBaseDate])
AND LOOKUP(Min([Order Date]),0) <= [pBaseDate]

The LOOKUP function is a table calc that is basically returning the same value for Order Date and comparing it against the base date. But because it is a table calc, when applied as a filter, it will only be applied after other computations.

Add this to the Filter shelf and set to True. Then adjust the table calc so it is explicitly computing by Order Date and then re-edit the filter again so it is just considering True values (changing the table calc setting, resets this).

Let’s start to build the viz :

Duplicate the tabular sheet above. Move Order Date to Columns and change to be continuous (green pill), Move MtD Sales to Rows and move Measure Values to Rows. Move the Measure Names field to the Colour shelf of the Measure Values marks card. Unstack the marks ( Analysis menu > stack marks > off) – if you can’t see 2 areas, swap the order of the pills in the Measure Values box so the 25th Percentile is listed first. Adjust the colours of the marks so the 25th percentile is white and the 75th percentile is pale blue (#e6f2fe). Ensure the opacity of these marks are 100%. Set the colour of the Mtd Sales line to bright blue.

Note – by moving the pills around and having already explicitly set the table calculation settings, we know the fields will be computing correctly. If you wish, you can build the viz from scratch, but you will need to explicitly set all the table calc pills to compute by Order Date again.

Make the chart dual axis and synchronise the axis. Right click the right hand axis and move marks to back to make the line display in front.

Create new fields

Ref Line – Start of Month

DATE(DATETRUNC(‘month’, [pBaseDate]))

and

Ref Line – End of Month

DATE(DATEADD(‘day’,-1, DATEADD(‘month’, 1,DATETRUNC(‘month’, [pBaseDate]))))

Custom format both of these to dd/mm and then add both to the Detail shelf of the All marks card. Set to be continuous exact date (green pills).

Add 2 reference lines to the Order Date axis, which reference these pills.

Add pBaseDate to the Detail shelf of the All marks card too, and add an additional reference line to that field. In this instance format the reference line and apply a custom format to the date to be dd/mm

Adjust the Tooltip via the All marks card. Remove all gridlines, zero lines and row/column dividers. Hide the right hand axis and the Order Date axis. Remove the title from the left hand axis.

To show the summary of how the MtD Sales value for the pBaseDate compares to the range, we will use the caption feature of the worksheet, which can reference fields, but we need these fields to essentially be ‘constants’ for every row of data, so we need some new fields.

Sales for Base Date

WINDOW_MAX(SUM(IF [Order Date] = [pBaseDate] THEN [Sales] END))

format this to $ with 0 dp.

25th Percentile for Base Date

WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [25th Percentile] END)

75th Percentile for Base Date

WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [75th Percentile] END)

Switch back to the tabular view of data and add these 3 fields. As they’re all table calculations, you need to set them to be computing by Order Date only (as we did above).

You should find that the values for the latest row (highlighted below) are displayed against every row in the 3 additional columns

With this, we can now work out the ‘text’ we want to disply in the caption

Expected Range Text

IF [Sales for Base Date] > [75th Percentile for Base Date] THEN ‘above’
ELSEIF [Sales for Base Date] < [25th Percentile for Base Date] THEN ‘below’
ELSE ‘within’
END

Add this to Rows, check the table calc settings, and then adjust the pBaseDate value so you can see the text change.

Now switch back to the line chart, and display the Caption (worksheet menu > show caption). Add Sales for Base Date and Expected range Text to the Detail shelf of the All marks card, adjusting the table calc settings as we’ve done before.

Then edit the caption and remove all text and update, referencing the various fields and parameters.

Building the bar chart

On a new sheet, add Segment to Rows and MtD Sales Diff to Columns. Sort descending. Create a new field

Diff is +ve

[MtD Sales Diff]>=0

Add to Colour and adjust accordingly. Show mark labels and set to match mark colour. Format the display to remove all column/row dividers, gridlines and zero lines. Display the Row Axis Ruler as a thicker grey line.

Then add all the components to a dashboard, using containers and padding to organise the display. Make sure to display the caption for the line chart worksheet.

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you create this ‘zoom and reset’ scatter plot?

Donna ColesLeave a comment

For this week’s #WOW challenge we’re focusing on parameter driven axes ranges.

Building the basic viz

After connecting to the dataset, add Sales to Columns and Profit to Rows. Add Customer Name to Detail and change the mark type to circle.

The chart is divided by reference lines which I chose to define as parameters (but these were additional to the ones mentioned in the challenge requirements).

pProfitRef

integer parameter defaulted to 0

pSalesRef

integer parameter defaulted to 5000

Add both parameters to the Detail shelf, the add a reference line on the Sales axis that refers to the pSalesRef value.

and then repeat and add a reference line to the Profit axis referencing the pProfitRef field.

To colour the 4 segments of the chart, create new fields

Sales per Customer

{FIXED [Customer Name]:SUM([Sales])}

and

Profit per Customer

{FIXED [Customer Name]: SUM([Profit])}

These capture the total sales / profit at the customer level, and we can then determine which quadrant each customer is in by

Cohort

IF [Sales per Customer]>=[pSalesRef] THEN
IF [Profit Per Customer]>=[pProfitRef] THEN ‘High Sales, High Profit’
ELSE ‘High Sales, Low Profit’
END
ELSE
IF [Profit Per Customer]>=[pProfitRef] THEN ‘Low Sales, High Profit’
ELSE ‘Low Sales, Low Profit’
END
END

Add this to the Colour shelf and adjust colours to suit. Then set the opacity to 50% and increase the size of the marks a bit.

To add matching coloured borders around the marks, add another instance of Profit to Rows. Change the mark type of the 2nd Profit marks card to Shape and change the shape to be an open circle. Set the opacity to 100%. Set the chart to dual axis and synchronise the axis.

Adjust the Tooltip and hide the right hand axis (uncheck show header).

Dynamically adjust the axis

The axes will change by adjusting them to refer to parameters. By default we need the axis to try to replicate what it gets set to automatically. For this we need to capture the maximum and minimum sales and profit values and add a ‘buffer’ to give the extra space. I played around with a few options for the buffer, so created a parameter to store this until I got the value that seemed to work best (again this was an additional parameter that I added to just help not having to change multiple calculated fields as I got the value I wanted.)

pBuffer

integer parameter defaulted to 2000

Then create 4 fields to define the max & min of the two measures +/- buffer

Min Sales + Buffer

{MIN([Sales per Customer]) – [pBuffer]}

Max Sales + Buffer

{MAX([Sales per Customer]) + [pBuffer]}

Min Profit + Buffer

{MIN([Profit Per Customer]) – [pBuffer]}

Max Profit + Buffer

{MAX([Profit Per Customer]) – [pBuffer]}

Then create 4 parameters which will define the values we ca use to set the axis

pX-Min

float parameter that is set to the Min Sales + Buffer field when workbook opens (selecting this will then populate the value)

Create further parameters

pX-Max

float parameter that is set to the Max Sales + Buffer field when workbook opens

pY-Min

float parameter that is set to the Min Profit + Buffer field when workbook opens

pY-Max

float parameter that is set to the Max Profit + Buffer field when workbook opens

Once all the parameters exist, edit the Sales Axis and change the axis to use a custom range that references the pX-Min and pX-Max parameters

Do the same for the Profit axis, but reference the pY-Min and pY-Max parameters instead.

Finally while we’re still on the workbook, create two new fields

True

TRUE

and

False

FALSE

and add these to the Detail shelf. We’ll need these later to stop marks highlighting when we click.

Name the sheet Scatter or similar.

Building the Reset button

This actually requires another sheet (even through the requirements says 1 sheet).

Create a new field

Reset Axis

‘Reset Axis’

Add this to the Text shelf of a new sheet. Change the mark type to shape and select a transparent shape (refer to this blog to understand how to create this).

Set the view to Entire View and align the font middle centre and increase the font size. Set the background of the whole worksheet to black. Adjust the tooltip. Add Min Sales + Buffer, Max Sales + Buffer, Min Profit + Buffer and Max Profit + Buffer to the Detail shelf, along with the True and False fields.

Name the sheet Reset or similar

Adding the interactivity

Add the Scatter and Reset sheets to a dashboard, removing any parameters/legends etc that get added. Create dashboard parameter actions to set the axis parameters when selections are made on the scatter plot:

Set X Max

On select of the Scatter sheet, set the pX-Max parameter, passing in the maximum value of the Sales field.

Set X Min

On select of the Scatter sheet, set the pX-Min parameter, passing in the minimum value of the Sales field.

Set Y Min

On select of the Scatter sheet, set the pY-Min parameter, passing in the minimum value of the Profit field.

Set Y Max

On select of the Scatter sheet, set the pY-Max parameter, passing in the maximum value of the Profit field.

Also create dashboard parameter actions to ‘reset’ the axis parameters when the Reset button is clicked:

Reset X Min

On select of the Reset sheet, set the pX-Min parameter, passing in the minimum value of the Min Sales + Buffer field.

Reset X Max

On select of the Reset sheet, set the pX-Max parameter, passing in the maximum value of the Max Sales + Buffer field.

Reset Y Min

On select of the Reset sheet, set the pY-Min parameter, passing in the minimum value of the Min Profit + Buffer field.

Reset Y Max

On select of the Reset sheet, set the pY-Max parameter, passing in the maximum value of the Max Profit + Buffer field.

All these 8 actions should now combine to drive the ‘zoom & reset’ functionality.

Finally, the last step to make the display ‘nicer’ is to deselect the marks from being highlighted when selected. Add a dashboard filter action

Deselect Scatter

on select of the scatter object on the dashboard, target the scatter sheet directly, passing the selected fields of True = False. Show all values when the selection is cleared.

Repeat an create a similar action for Deselect Reset.

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you create a drill-down

Donna ColesLeave a comment

Kyle set the challenge this week to recreate a drill-down, but with the stipulation that no parameters were to be used. I immediately figured this would be a challenge requiring set actions, and indeed the hint on the splash page of the #WOW site, confirmed this

Building the Viz

After connecting to the data source, create a Set off of the Category field (right click > create > Set). Select a single option eg Technology

Category Set

Create fields

Display Value

IF [Category Set] THEN [Sub-Category] ELSE [Category] END

and

Expand Indicator

IF [Category Set] THEN [Category] ELSE ‘+’ END

Add Category, Expand Indicator and Display Value to Rows and Sales to Columns and press the sort desc button in the toolbar, to sort all the bars . The click the Category pill to add another sort by sales descending.

Hide the Category pill (uncheck show header).

Format the Expand Indicator column, so the text is aligned vertically

Right click on the Expand Indicator text heading displayed in the view and hide field labels for rows. Widen each row a bit, remove all gridlines, remove the Sales axis title (right click axis > edit axis). Add a title to the sheet. Adjust the Tooltip.

Adding the interactivity

Add the sheet to a dashboard, then add a dashboard set action

Select Cat

On select of the viz on the dashboard, target the Category Set, adding values to the set when the viz is clicked (selected), and remove all values when the selection is cleared. Only allow 1 selection at a time to be made.

And that’s it. My published viz is here.

Happy vizzin’!

Donna

Can you build an L-Shaped bar chart?

Donna ColesLeave a comment

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.

Add to a dashboard. My published viz is here.

Happy vizzin’!

Donna

Let’s practice Table Calcs!

Donna ColesLeave a comment

For this week’s #WOW2025 challenge, Sean went ‘back to basics’ with a focus on table calculations.

Let’s jump right in.

After connecting to the data, add Team to Rows and Timestamp as a continuous (green) pill at the Day level to Columns. Create a new field

Call Count

COUNT([synthetic_call_center_data.csv])

and then add this to Rows.

Add Timestamp to Filter, and select relative date. Set the options to Last 3 weeks, and then additionally check the anchor relative to check box and enter 23 Dec 2024. This is because the data set only goes up to the end of December 2024. Not setting this field will apply the date filter based on ‘today’ so it’s unlikely anything will appear.

(Note – you may also need to update the date properties of the data set to ensure a week starts on a Sunday to get matching numbers: right click the data source and select the date properties option).

To add a marker to the last point, create a field

Call Count – Most Recent

IF LAST()=0 THEN [Call Count] END

Add this to Rows and adjust the table calc setting so it is computing specifically by Day of Timestamp only. By default it was doing this via the Table (across) option, but I tend to always prefer to always explicitly fix what the calculation is computing over, as it won’t then matter where I then move that field too if I choose to change the layout of the viz.

Set the mark type on the Call Count marks card to line, and then adjust the colour to grey and reduce the size. Set the mark type of the Call Count – Most Recent marks card to circle, set the colour to blue and increase the size. Hide the null indicator (right click > hide).

Set the chart to dual axis, synchronise the axis and then remove the Measure Names field from the All marks card.

remove both the axis titles (right click axis > edit axis), hide the right hand axis (right click, untick show header), and format to remove the column divider from the header section only.

Now we’ve got the core display, we need to create the following fields

No. of Calls

WINDOW_SUM([Call Count])

Highest Call Vol

WINDOW_MAX([Call Count])

Lowest Call Vol

WINDOW_MIN([Call Count])

Avg Call Vol

WINDOW_AVG([Call Count])

format this to a number with 0 dp

Calls this period

WINDOW_MAX([Call Count – Most Recent])

the window_max is required here, as the data set we’re displaying at the day level, has 2 values – the latest value and null. We only want to return 1 value, which is the maximum of these.

Previous Period

WINDOW_MAX(IF LAST()=1 THEN [Call Count] END)

LAST()=1 returns the value of the next to last record, and the window_max is again applied, as the nested IF clause will return null for all others records.

Period Var

[Calls this period] – [Previous Period]

Add each of these fields, one by one, to Rows following the steps below

  • Add to rows (it will automatically display as a green continuous pill).
  • change to discrete (right click on the pill and select discrete – the pill will turn blue and move to before the green pills)
  • Explicitly set the table calc to be computing by Timestamp (as above)

Once, you should have something that looks like this

but I noticed, that the display in the solution is sorted based on the total number of calls and not by Team, so add a Sort to the Team pill to sort by Call Count descending

Update the Tooltip if you wish, and then add the viz to a dashboard, floating the Timestamp filter.

My published viz is here.

Happy vizzin’!

Donna

Can you build a colour-coded filter?

Donna ColesLeave a comment

Erica collaborated with Giulio D’Errico for this week’s #WOW2025 challenge, which contained lots of features, though the main challenge was to display filter on Region, which along with the Region name also displayed a KPI indicator that could change based on selection from other parameters.

Defining the parameters

We need 3 parameters for this challenge

pMeasure

strig parameter that lists the two options Profit and Sales; defaulted to Profit.

pProfitThreshold

integer parameter that lists the specified values, defaulted to 2,000

pSalesThreshold

integer parameter that lists the specified values, defaulted to 30,000

Building the core scatter plot

Add Sales to Columns and Profit to Rows and Sub-Category to Detail. Show the 3 parameters.

When pMeasure = Profit, we need to display horizontal reference lines against the Profit axis, and when Sales is selected we need to display vertical reference lines against the Sales axis. We need the following fields to return the user defined thresholds:

Ref – Profit Threshold

IF [pMeasure] = ‘Profit’ THEN [pProfitThreshold] END

Ref – Sales Threshold

IF [pMeasure] = ‘Sales’ THEN [pSalesThreshold] END

We also need to define the average per measure for each region:

Sales Avg Per Region

{FIXED [Region]: SUM([Sales])} / {FIXED: COUNTD([Sub-Category])}

Profit Avg Per Region

{FIXED [Region]: SUM([Profit])} / {FIXED: COUNTD([Sub-Category])}

and then we can create

Ref – Profit Avg

IF [pMeasure] = ‘Profit’ THEN [Profit Avg Per Region] END

Ref – Sales Avg

IF [pMeasure] = ‘Sales’ THEN [Sales Avg Per Region] END

Add the 4 Ref – XXX fields to the Detail shelf. Then add 2 reference lines to the Sales axis; one referencing Ref – Sales Avg (coloured as a purple dashed line at 100% opacity) and one referencing Ref – Sales Threshold (coloured as a black dashed line at 50% opacity). Display a custom label and then format the label to be aligned vertically and coloured based on the relevant line, and with a 0% shading. Set the pMeasure to Sales to make these display.

Then repeat, adding 2 reference lines to the Profit axis instead. Change pMeasure to Profit for these to appear.

Change the mark type to square. Each mark needs to be coloured whether it is above or below the average for the measure selected.

Colour

IF [pMeasure] = ‘Profit’ THEN
IIF(SUM([Profit]) >= SUM([Profit Avg Per Region]),1,0)
ELSE
IIF(SUM([Sales]) >= SUM([Sales Avg Per Region]),1,0)
END

Set this to be discrete and then add to the Colour shelf and adjust accordingly.

Creating the colour-coded filter

The viz needs to be filtered by Region, but the filter displayed needs to show the region name along with an indicator based on whether the average for the region is above the threshold or not. This took a bit of an effort, but I finally managed it.

We need a field to capture the ‘KPI indicator’ based on which measure was selected. It also needs to be computed per region. We need a FIXED LoD for this, and I made use of coloured unicode characters from this site.: https://unicode-explorer.com/ (large yellow circle and large green circle which you can just ‘copy and paste’ into the calculation)

Region Indicator

{FIXED [Region]: (

IF [pMeasure] = ‘Profit’ THEN
IIF(SUM([Profit Avg Per Region])>=[pProfitThreshold],’🟢’,’🟡’)
ELSE
IIF(SUM([Sales Avg Per Region])>=[pSalesThreshold],’🟢’,’🟡’)
END
)}

I then created

Filter – Region

[Region Indicator] + ‘ ‘ + [Region]

Add this to the Filter shelf, select all values and show the filter. If you change the pProfitMeasure to Profit and pProfitThreshold to 6,000, you’ll see the KPI indicators change.

Format the Tooltip

The tooltip requires several calculated fields to be created. Certain fields only need a value based on the pMeasure and others have conditional formatting (different colours) applied. For the tooltip, I created all these fields:

Tooltip – Measure Value

IIF([pMeasure]=’Profit’,[Profit],[Sales])

Tooltip -Threshold

IIF([pMeasure]=’Profit’, [pProfitThreshold], [pSalesThreshold])

Tooltip – Measure Value Above Avg

IF [Colour] = 1 THEN SUM([Tooltip – Measure Value]) END

Tooltip – Measure Value Below Avg

IF [Colour] = 0 THEN SUM([Tooltip – Measure Value]) END

Tooltip – Above text

IF [Colour] = 1 THEN ‘above’ END

Tooltip – Below text

IF [Colour] = 0 THEN ‘below’ END

Label – Region

IIF(COUNTD([Region])>1, ‘All regions’, MIN([Region]))

Add all these fields to the Tooltip. Update the Tooltip to reference the fields and the pMeasure parameter, colouring the text as required. Some of the fields will only display based on the filters selected, so they get places side by side with no spacing.

Finalise the viz by updating the title to reference the pMeasure and the Label – Region field. Remove all gridlines, axis rulers, zero lines etc. Colour the background of the sheet to pale blue.

Building the Overall Indicator

For the bonus challenge, we need to display an indicator that is green if all the regions are green and yellow if at least 1 region is yellow.

On a new sheet, add Region and Region Indicator to Rows and show the 3 parameters.

We’re going to create a field that will return 1 if the Region Indicator is yellow and 0 otherwise.

Region Indicator – Is Below

IIF( [Region Indicator] = ‘🟡’,1,0)

Set to be discrete and add to Rows. Change the pProfitThreshold to 6000 to see the flag change.

Create

Overall Region Indicator

IIF(WINDOW_MAX(MAX([Region Indicator – Is Below]))=1,’🟡’, ‘🟢’)

This says, if the maximum value of the rows ‘in the window’ is 1, then display a yellow indicator, else green. Add to Rows, and adjust the pProfitThreshold to see the behaviour.

Create a field

Filter – Index = 1

INDEX() = 1

Add to Filter shelf and set to True

Move Region to Detail. Remove Region Indicator and Region Indicator – Is Below. Adjust the table calculation setting of Overall Region Indicator to compute using Region only, and do the same for the tableau calculation on Filter – Index = 1 (re-edit the filter after changing, so only True is selected).

Note – originally I used a transparent shape mark type and displayed the indicator as a label, but after publishing to Tableau Public, the indicator became distorted, so I adjusted how this was built.

Set the mark type to circle and add Overall Region Indicator to Colour. Adjust to be green or yellow, depending on the colour of the KPI indicator (you’ll need to change the pProfitThreshold value to ensure you set the colour for both the yellow and green options).

Finally create

Tooltip – Overall Indicator

IIF([Overall Region Indicator]=’🟢’,’All regions meet the ‘ + [pMeasure] + ‘ target’, ‘At least one region does NOT meet the ‘ + [pMeasure] + ‘ target’)

Add this to Tooltip and then set the background colour to pale blue.

Building the dashboard & adding dynamic zone visibility

Using layout containers, add the viz to a dashboard. Use a horizontal layout container to arrange the parameters, the Region filter and the Overall Indicator sheet. I used a floating text object to display the ‘legend key’ again copying come unicode characters from the website referenced earlier.

Then create 2 new boolean calculated fields

Is Profit

[pMeasure] = ‘Profit’

Is Sales

[pMeasure] = ‘Sales’

Select the Sales Threshold parameter object, then update the visibility so it only displays if Is Sales is true (via the Layout > Control visibility using value option)

Repeat the same for the Profit Threshold object, but reference the Is Profit field instead.

Now as you switch the measure between Sales and Profit, the relevant threshold parameter will display. My published viz is here.

Happy vizzin!

Donna

Can you visualise yearly rank change in Sub-Category sales?

Donna ColesLeave a comment

Yusuke set this interesting challenge : to combine a ‘bump’/’slope’ chart visualising the change in rank whilst also visually displaying the Sales value for the relevant Sub-Category in the ranked position.

Defining the calculations

This challenge will involve table calculations, so I’m going to start by building out the various calculations that will be required and displaying in a tabular view.

Add Category to Filter and select Office Supplies. Then add Sub-Category and Order Date at the Year level as a discrete (blue) pill to Rows. Add Sales to Text.

Create a new field

Sales Rank

RANK(SUM([Sales]))

And add to the table, and verify the table calculation is set to compute by Sub-Category only.

We will need to ‘colour’ the viz based on the rank compared to the previous year. For this create

Is Min Year

{MIN(YEAR([Order Date]))} = YEAR([Order Date])

which will return true for the first year in the data (in this instance 2022) and then create

Colour

IF [Sales Rank] = LOOKUP([Sales Rank],-1) OR ATTR([Is Min Year]) THEN ‘Same as last year’
ELSE ‘Different from last year’
END

If the rank is the same as the previous one, or it’s the first year, then treat as the same, otherwise treat as different.

Add the Colour field to the table, and this time make sure the table calculation for Colour is computing by Year of Order Date only (while the nested calc for Sales Rank should still be computed by Sub-Category only)

The labels on the viz only want to show in certain scenarios – if it’s the first record (ie for 2022) or there has been a change in rank. We need

Label : Rank & Sub Cat

IF ATTR([Is Min Year]) OR [Colour] = ‘Different from last year’ THEN STR([Sales Rank]) + ‘ | ‘ + MIN([Sub-Category]) END

and

Label : Sales

IF ATTR([Is Min Year]) OR [Colour] = ‘Different from last year’ THEN SUM([Sales]) END

format this to $ with 0dp

Add these to the sheet, and double check the nested table calculations on each pill are computing as required (Sales Rank by Sub-Category only, Colour by Year Order Date only)

Now we have all this, we can start building

Creating the Viz

On a new sheet, add Category to Filter and select Office Supplies. The add Order Date to Columns, but set to be continuous (green) pill at the Year level. Add Sub-Category to Detail and add Sales Rank to Rows as a discrete (blue) pill. Verify the table calculation setting against the Sales Rank pill is by Sub-Category only.

Change the mark type to line and then add Order Date to Path. By default it should be at the Year level as a discrete pill.

This is the ‘bump’ chart.

Now add another instance of Order Date to Columns as a continuous pill at the Year level to essentially duplicate the display. On the 2nd marks card, change the mark type to Gantt

This gives us the ‘starting point’ for each ‘bar’. But we need to determine the size for each bar. First we’re going to ‘normalise’ the sales values for all the sales being displayed so we get a value between 0 and 1, where 0 is the smallest sale, and 1 is the largest.

Normalised Sales

((SUM([Sales]) – WINDOW_MIN(SUM([Sales]))) / (WINDOW_MAX(SUM([Sales])) – WINDOW_MIN(SUM([Sales]))))

To see what this is doing, format the field to 2dp, then add the field to the tabular view, and ensure the table calculation is computing by both Sub-Category and Year Order Date.

But the ‘axis’ we want to plot the bar length against is in years, so we need to adjust this size to be a proportion of a year (ie 365 days)

Gantt Size

//proportion of a year
[Normalised Sales] * 365

Add this to the Size shelf on the 2nd marks card on the viz. Adjust the table calc setting so it is computing by all the fields listed.

We now have the core concept so now we can start finalising the display.

Make the chart dual axis and synchronise the axis.

Set the view to fit height.

On the 1st marks card (that represents the line)

  • change the line style to dotted (via the Path shelf)
  • reduce the Size to suit
  • change the colour to pale grey
  • Add Label : Sales and Label : Rank & Sub Cat to the Label shelf.
    • Adjust the table calc settings of each so the nested table calcs in each have Sales Ranks by Sub Category only and Colour by both the Year Order Date fields only.
    • Adjust the layout of the text as required
    • Align the font to be top right
    • Change the font style (bold & black)
    • Ensure the Label is set to ‘allow labels to overlap marks’
  • Remove the Tooltip

On the 2nd marks card, the gantt bar

  • Add Colour to the Colour shelf and adjust the colours accordingly.
    • Verify the table calc settings are as expected
    • I chose to reduce the opacity slightly, so I could see the dotted line underneath (set to 70%)
  • Add Sales to Tooltip (format to $ with 0 dp) and the adjust Tooltip as required

Then we just ned to finalise the formatting/display

  • Set the font of the years and rank numbers to black & bold.
  • hide the Sales Rank label heading (right click > hide field labels for rows)
  • remove row & column dividers
  • Add black column gridlines (I set to the 2nd thickness level), and remove any row gridlines
  • Edit the top axis to have a fixed start (use default option) and end at 31/12/2025 so the 2026 label and line disappears.
  • Remove the title from the top axis.
  • Edit the bottom axis – remove the tile, and then set the tick marks to None, so the bottom axis now looks empty.

And that should be it. Now add the sheet to a dashboard and display the category filter as a single select, customising the remove the ‘all’ option.

My published viz is here

Happy vizzin’!

Donna