For the next few Workout Wednesdays, the coaches will be revisiting old challenges – retro month! Erica kicked the month off with this challenge, to recreate Ann Jackson’s challenge from 2018 Week 38. I completed this challenge at the time (see here), but the additions and changes to the visual display Erica incorporated meant I couldn’t just republish it 🙂

So I built it from scratch using the data source from the google drive Erica referenced in the requirements (which I believe may be why my summary KPIs didn’t actually match Erica’s).

There’s a heck of a lot going on in this challenge – it certainly took some time to complete, which may mean this blog becomes quite lengthy… I will endeavour to be as succinct as I can, which may mean I don’t explicitly state every step, or show lots of screen shots.

Setting up the parameters

I used parameters and subsequently dashboard parameter actions to build my solution. Erica mentions set actions, but I chose not use any sets.

As a result there’s lots of parameters that need creating

pAggregate

This is a string parameter that contains the list of possible dimensions to define the lowest level of detail to show on the scatter plot (ie what each dot represents). Default to Sub-Category. Note how the Display As field differs from the Value field.

pColour Dimension

This is a string parameter that will contain the value of the dimension used to split the display into rows (where each row is coloured). This will get set by a parameter action from interactivity on the dashboard, so no list of options is displayed. Default to Segment.

pSplit-Colour

boolean parameter to control whether the chart should be split with a row per ‘colour’ dimension, or just have a single row. The values are aliased to Yes/No

pSplit-Year

another boolean parameter to control whether the chart should be split with a column per year or just have a single column. The values are aliased to Yes/No (essentially similar to above)

pX-Axis

string parameter that contains the value of the measure to display on the x-axis. This will be set by a dashboard parameter action, so no list is required. Default to Sales.

pY-Axis

Similar to above, a string parameter that contains the value of the measure to display on the y-axis. This will be set by a dashboard parameter action, so no list is required. Default to Profit.

pSelectedDimensionValue

string parameter that contains the dimension value associated to the mark that a user clicks on when interacting with the scatter plot, and then causes other marks to be highlighted, or a line to be drawn to connect the marks. This will be set by a dashboard parameter action, so no list is required. Default to <nothing>/empty string.

Building the basic Scatter Plot

The scatter plot will display information based on the measures defined in the pX-Axis and pY-Axis parameters. We need to translate exactly what the measures will be based on the text strings

X-Axis

CASE [pX-Axis]
WHEN ‘Profit’ THEN SUM([Profit])
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Quantity’ THEN SUM([Quantity])
END

Y-Axis

CASE [pY-Axis]
WHEN ‘Profit’ THEN SUM([Profit])
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Quantity’ THEN SUM([Quantity])
END

We also need to define which field will control the lowest level of detail based on the pAggregate dimension

Dimension Detail

CASE [pAggregate]
WHEN ‘Category’ THEN [Category]
WHEN ‘Sub-Category’ THEN [Sub-Category]
WHEN ‘Product’ THEN [Product Name]
WHEN ‘Region’ THEN [Region]
WHEN ‘State’ THEN [State]
WHEN ‘City’ THEN [City]
END

Similarly we need to know which field to split our rows by (the colour)

Dimension Row

CASE [pColour Dimension]
WHEN ‘Segment’ THEN [Segment]
WHEN ‘Category’ THEN [Category]
WHEN ‘Region’ THEN [Region]
WHEN ‘Ship Mode’ THEN [Ship Mode]
END

but as we need different behaviour depending on whether the pSplit-Colour field is yes or no, we need

Row Display

IF [pSplit-Colour] THEN [Dimension Row]
ELSEIF [pColour Dimension] = ‘Category’ THEN ‘All Categories’
ELSE ‘All ‘ + [pColour Dimension] + ‘s’
END

If the parameter is true, then just show the value from the Dimension Row, otherwise display as ‘All Categories’ or ‘All Segments’ or ‘All Regions’ etc.

Similarly, as the columns can be split by years or not, we need

Years

IF [pSplit-Year] THEN STR(YEAR([Order Date])) ELSE ” END

Add the fields to a sheet with

• Years & X-Axis on Columns
• Row Display & Y-Axis on Rows
• Dimension Detail on Detail
• Dimension Row on Colour
• Set the mark type to circle and reduce colour opacity
• Edit the axes, so the titles are sourced from the pX-Axis and pY-Axis parameters
• Show all the parameters and manually edit the values/change the selections to test the functionality.

Highlighting corresponding marks

Show the pSelectedDimension parameter and hover over a mark in the scatter plot to read the value of the Dimension Detail field. Enter than value into the pSelectedDimension parameter (eg based on what is displayed above, each mark is a Sub-Category, so I’ll set the field to ‘Phones’).

We need to determine whether the value in the parameter matches the dimension in the detail

Highlight Mark

[pSelectedDimensionValue] = [Dimension Detail]

This returns True or False. Add this field to the Detail shelf, then add it as second field on the Colour shelf.

Adjust the default sorting of the Highlight Mark field, so the True is listed before False (sorted descending) – right click on the field > Default Properties > Sort. And ensure the colour fields on the shelf are listed so Dimension Row is above Highlight Mark. If all this is done, then the colour legend show look similar to below, where the Dimension Row is listed before the True/False, and the Trues are listed above the Falses, so the True is a darker shade of the colour.

Add Highlight Mark to the Size shelf and then edit the size legend to be reversed and adjust the sizes so the smaller ones aren’t too small, but you can differentiate (you may need to adjust the overall size slider on the size shelf too).

Making a connected dot plot

Add Order Date at the Year level (blue discrete pill) to the Detail shelf of the scatter plot.

To make the lines join up when the viz isn’t split by year, we need a field

Y-Axis Line

IF NOT [pSplit-Year] AND [pSelectedDimensionValue] = MIN([Dimension Detail]) THEN [Y-Axis] END

This will only return a value to plot on the Y-Axis if pSplit-Year = No and a user has clicked on a mark.

Set the pSplit-Year parameter to No, then add Y-Axis Line to Rows. On the Y-Axis Line marks card, remove Highlight Mark from colour and size and also remove Dimension Row from Colour. Add Order Date at the Year level (blue discrete pill) to Detail. Change the mark type to Line then add Year(Order Date) to Path instead of Detail.

Make the chart dual axis and synchronise the axis.

Play around changing the pSplit-Year parameter and the value in the pSelectedDimension parameter to test the functionality.

Tidy the scatter plot by adjusting font sizes, removing the right hand axis & the gridlines, lightening the row & column dividers, removing row & column label headings. Tidy up tooltips. Add a title that references the parameter values.

Building the Total Marks KPI

Create a new field

Count Marks

SIZE()

and a field

Index

INDEX()

Set this field to be a discrete dimension (right click > convert to discrete)

On a new sheet, add Dimension Row, Dimension Detail and Order Date (set to Year level as blue discrete pill) to the Detail shelf. Add Count Marks to Text. Adjust the table calculation setting of Count Marks so that all the fields are selected.

Add Index to the Filter shelf and select 1. Then adjust the table calculation setting of this field so it is also computing by all fields. Re-edit the filter, and adjust so only 1 is selected. This should leave you with 1 mark. Change the mark type to shape and set to use a transparent shape.

Adjust font size & style, set background colour of worksheet to grey, adjust title, hide tooltips.

Building the X-Axis KPI

For this we need

Total X-Axis

TOTAL([X-Axis ])

Min X-Axis

WINDOW_MIN([X-Axis ])

Max X-Axis

WINDOW_MAX([X-Axis ])

On a new sheet add Dimension Row, Dimension Detail, YEAR(Order Date) to Detail. Add pX-Axis, Total X-Axis, Min X-Axis & Max X-Axis to Text. Adjust all the table calcs of the Total, Min, Max fields to compute using all dimensions listed. Add Index to filter and again set to 1, then adjust the table calc and re-edit so it is just filtered to be 1. Set the mark type to shape and use a transparent shape. Adjust the layout & font of the text on the label. Set background colour of worksheet to grey, adjust title, hide tooltips.

Building the Y-Axis KPI

Repeat the steps above, creating equivalent Total, Min & Max fields referencing the Y-Axis.

Creating the Y-Axis ‘buttons’

We’ll start with creating a Profit button

Create a field

Label: Profit

“Profit”

and

Y-Axis is Profit

[pY-Axis] = ‘Profit’

We will also need the field below for later on

Y-Axis not Profit

[pY-Axis] <> ‘Profit’

On a new sheet double click on Columns and manually type in MIN(1). Add Label: Profit to Text and Y-Axis is Profit to Colour. Change the mark type to bar.

Set the Size of the bar to maximum, adjust the axis to be fixed from 0-1 and hide the axis. Remove all column/row banding, axis line, gridlines etc.

Show the pY-Axis parameter. If the colour legend is set to True (as pY-Axis contains Profit), then adjust the colour to a dark grey. Then change the value of the pY-Axis parameter, which should then display False in the colour legend. Adjust this to light grey. You may need to do this the other way round. Hide tooltips.

Repeat the same process to create separate sheets for Sales and Quantity with equivalent calculated fields (I found the easiest way was to duplicate the sheet and then swap out the fields).

Creating the X-Axis ‘buttons’

Again, just duplicate the above steps but reference the pX-Axis parameter instead.

You should end up with 6 sheets (1 per measure – Sales, Profit, Quantity – per axis), and 18 calculated fields (3 per measure & axis) as a result.

Creating the ‘Select Colour’ buttons

For the Category button, create

Label: Category

‘Category’

and

Colour is Category

[pColour Dimension] = ‘Category’

Build a ‘button’ as a bar chart, using the same principals as above. You will need to show the pColour Dimension parameter to test changing the value to set the different colours.

Repeat the same steps to build 3 further sheets for Region, Segment and Ship Mode.

Building the dashboard

You will need to use layout containers to arrange all the objects in. The main body of the dashboard consists of a horizontal layout container, which contains 3 objects: a vertical container (left column with configuration options), the scatter plot in the middle and then another vertical container (right column with KPIs).

The left hand ‘Configure Your Chart’ vertical container consists of text objects, parameters, the X-Axis ‘button’ sheets, the Y-Axis ‘button’ sheets and the Colour ‘button’ sheets.

For each of the X-Axis and Y-Axis button sheets, a parameter action needs to be created like below

Set Y-Axis to Profit

On select of the Y-Axis Profit sheet (or whatever you have named the sheet), set the pY-Axis parameter with the value from the Label:Profit field.

You should end up with 6 different parameter actions for these fields – 1 per measure per axis .

For each of the ‘Colour’ buttons, a similar parameter action is also required

Set Colour to Category

On select of the Colour-Category sheet (or whatever you have named the sheet), set the pColour Dimension parameter with the value from the Label:Category field.

You should end up with 4 parameter actions like this.

The Y-Axis and X-Axis buttons should only display 2 options at a time, so you can’t select the same measure for both axis.

Assuming Profit is currently selected on the Y-Axis, then select the object that displays the Profit X-Axis ‘button’ and from the Layout tab, set to control visibility using value and select the Y-Axis not Profit calculated field. As the Y-Axis is set to Profit, the Profit X-Axis button will disappear.

Repeat these steps for each of the X & Y Axis button sheets. Note, sometimes it’s easier to select the object via the Item Hierarchy section of the Layout tab.

For the scatter plot, the user needs the ability to select a mark and the others be highlighted/connected depending on the current settings. This needs another parameter action

Select Dimension Value

On select of the scatter plot, set the pSelectedDimensionValue parameter with the value from the Dimension Detail field.

For the right hand KPI nav, the vertical container consists of text objects, and the KPI sheets.

To make horizontal line separators, set the padding on a blank object to 0, the background colour to the colour you want, and then edit the height of the blank object to 2pt.

For the Info ‘button’, add a floating text box containing the relevant text and position it where you want it to display. Give the object a border and set the background to white. Then select the object and choose the Add Show/Hide Button from the context menu. Edit the button to display an info icon when hidden. Ensure the button is also floating and position where you want.

I used additional floating text boxes to display some of the other text information on the dashboard.

No doubt you’ll need to spend some time adjusting padding and layout to get everything where you want, but this should get you the core functionality.

My published viz is here. Phew! that was epic!

Happy vizzin’!

Donna