It was time for Lorna to add her iteration to the dashboard challenge that’s been set over the last few weeks. The challenge this week was to incorporate a measure swap control which allows the user to select which measures they want to compare.

Lorna gave the option to build out either your own or an already published solution, or to build a simpler brand new viz that just demonstrates the technique.

By incorporating this measure swap control into an existing solution, existing calculated fields and other objects will need to be adjusted/renamed. I started to build against my own solution, but that already had multiple instances of calculated fields where I’d chosen to change from a table calculation version in week 5 to an LoD based solution in week 6. I decided instead to use Kyle’s solution from week 7, which is published here.

However, for the purposes of this blog, I will build out the simple solution, so the focus is on what you need to do to build the control.

Building the Measure Selector

So to start, I simply copied the text for the secondary data source off the challenge page

I then opened Tableau Desktop, connected to the Orders sheet of the relevant Superstore Sales excel spreadsheet, then on the Data menu, selected Paste, which added a ‘clipboard’ data source and presented the data on a sheet.

To start with I chose to Alias the Number field (right click > Aliases) to map the numbers in the data source to the names of the measures we want, as below

The values selected are going to be captured within a parameter, so I then set these up

pMeasureX

Integer parameter defaulted to 1 (ie Sales)

I created a similar pMeasureY too, this one defaulted to 3 (ie Profit Margin).

For the selector control, we need to indicate which measure has been selected, so we need

X Selected?

[pMeasureX]=[Number]

and

Y Selected?

[pMeasureY]=[Number]

I then added Number to Rows and created 3 MIN(1) fields on the Columns shelf

On the first MIN(1) marks card, I changed the mark type to Shape, then added X Selected? to the Shape shelf. I adjusted the shape using options from the Ratings shape palette, and adjusted the size to suit.

On the 2nd MIN(1) marks card, I changed the mark type to Circle, reduced the size to as small as possible, and changed the colour to be completely transparent (Opacity = 0%). I then added Number to the Label shelf, changed the alignment to be Middle Centre, and adjusted the height of the rows, so all the text labels are visible.

On the 3rd MIN(1) marks card, I repeated the steps taken for the 1st marks card, but added Y Selected? to the Shape shelf instead.

I then hid the axes and the Number pill (uncheck Show Header), and removed all row/column borders and gridlines/zero lines.

When this sheet is added to the dashboard, we’re going to need to add parameter actions to pass information from this sheet into the pMeasureX and pMeasureY parameters. For this I found I needed to create copies of the Number field, as using the same field to control both selectors caused me some issues. So I created

Number (X)

[Number]

and added this to the Detail shelf of the 1st MIN(1) marks card.

And then

Number (Y)

[Number]

which was added to the Detail shelf of the 3rd MIN(1) marks card.

Another feature of this sheet when added to the dashboard, is we don’t want the circle selected to be highlighted/remain selected, so we’ll use a filter action to solve that. But again we need some additional fields on the sheet to help us with that. Create calculated fields

True

TRUE

False

FALSE

And add both of these to the Detail shelf of the All Marks card.

Finally adjust the title of the sheet to contain the text ‘X Select a Measure Y’. You’ll need to play around with the number of spaces to use. It’s best to wait until the sheet is on the dashboard to get it right.

Building the Scatter Plot

On a new sheet, the first thing needed for this challenge is to create

Profit Ratio

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

Now we need to define fields that will determine what measure to display based on the value stored in the relevant parameter

X Measure

CASE [pMeasureX]
WHEN 1 THEN SUM([Sales])
WHEN 2 THEN SUM([Profit])
WHEN 3 THEN [Profit Ratio]
WHEN 4 THEN SUM([Quantity])
END

Y Measure

CASE [pMeasureY]
WHEN 1 THEN SUM([Sales])
WHEN 2 THEN SUM([Profit])
WHEN 3 THEN [Profit Ratio]
WHEN 4 THEN SUM([Quantity])
END

Add X Measure to Columns and Y Measure to Rows, and add State to Detail.

We need to label the X & Y axis based on the name of the selected measure, so we need

X Dimension

CASE [pMeasureX]
WHEN 1 THEN ‘Sales’
WHEN 2 THEN ‘Profit’
WHEN 3 THEN ‘Profit Ratio’
WHEN 4 THEN ‘Quantity’
END

Add this to the Columns shelf

Y Dimension

CASE [pMeasureY]
WHEN 1 THEN ‘Sales’
WHEN 2 THEN ‘Profit’
WHEN 3 THEN ‘Profit Ratio’
WHEN 4 THEN ‘Quantity’
END

Add this to the Rows shelf

Edit both axes and remove the title from both. Right click on the word ‘Profit Ratio’ and Rotate Label. Right click on ‘X Dimension’ at the top and select hide field labels for columns. Right click on ‘Y Dimension’ on the left and select hide field labels for rows. Format to remove the row and column gridlines.

The State a user clicks on/selects needs to be captured within a parameter

pSelectedState

String parameter defaulted to New Jersey

We then need to determine if the State matches that selected

Is Selected State?

[State]= [pSelectedState]

Change the mark type to Circle, then add Is Selected State? to the Colour shelf and adjust to suit. Add a border to the mark too. Drag the ‘True’ value on the colour legend so its listed above ‘False’.

Add Is Selected State? to the Size shelf too, and adjust so the sizes are reversed, and alter the range.

Use the slider on the Size shelf too to make all the circles smaller too if need be.

Adjust the Tooltip so the text will be dynamic based on the measure selections too.

Then, modify the title of the sheet, so it too is dynamic.

Finally, this sheet too also needs to ensure the State circle clicked on doesn’t remain ‘selected’ so as above, create a True = TRUE and a False = FALSE calculated fields, and add these to the Detail shelf.

Adding the interactivity

Add both the sheets to a dashboard.

Create the following dashboard actions:

Set X Measure

A parameter action, that runs on Select of the Measure Selector sheet and passes Number (X) into the pMeasureX parameter.

Create a dashboard filter action Selector Sheet – Unhighlight which on select of the Measure Selector sheet on the dashboard, targets the Measure Selector sheet directly, passing True = False into the filter. All values should show when the selection is cleared.

With these 3 actions, you should be able to test what happens when you click the different combinations.

Now we need 2 further dashboard actions

Set Selected State

A parameter action that on select of the Scatterplot passes the State field into the pSelectedState parameter.

Add finally, add another dashboard filter action, Scatter – Unhighlight that on select of the Scatter sheet on the dashboard, targets the scatter sheet directly, passing the fields True = False as the filter.

Hiding the Measure Selector

On the dashboard, select the Measure Selector sheet and select the Add Show/Hide Button from the context menu.

This will create a X button that can then be moved and positioned where required.

On the context menu of the X button, select Hide and the measure selector sheet will disappear, and the X will be replaced by a different image

Select Show from the context menu of this button, and the sheet will reappear. Test the functionality in presentation mode where simply clicking the button will invoke the show/hide behaviour.

And this should be the core functionality to demonstrate this measure swapping feature. My published viz based on this more basic solution is here.

If you’re aiming to build on the viz you’ve built over the last 3 weeks, then you need to replace any references you had to SUM([Sales]) and [Profit Ratio] in the various calculations with the X Measure and Y Measure fields accordingly. You’ll also need to adjust tooltips and titles of the various sheets using the X Dimension and Y Dimension fields as appropriate. My adapted solution built on from Kyle’s week 7 solution is here.

Happy vizzin’! Stay Safe!

Donna