For this week’s #WOW2023 challenge, Kyle wanted us to build a viz that used selections on the viz rather than a set of filter controls to show how the sales for those selections were distributed.

This concept is referred to as proportional brushing and makes use of set actions to achieve the results. The complexity added here was the multiple selections being made.

6 sheets make up this dashboard – 1 for each bar chart, 1 for the KPI and 1 for the breadcrumb trail.

Building the basic bar charts

Create 4 sheets, one for each of the Region, Segment, Ship Mode and Sub-Category dimensions. The simplest way is to build one sheet, get all the formatting applied etc, then duplicate and replace the dimension on the duplicated sheet with the new one.

When building the first sheet, place the dimension (eg Region) on Rows and Sales on Columns, sorted descending. Adjust the Sales to be formatted to $ with 0dp. Hide the Sales axis, and format to remove all gridlines/axis lines/ zero lines and row/column dividers. Show mark labels and align centrally. Adjust the font label to 8pt. Widen each column if need be. Hide the dimension label from displaying (hide field label for columns). Adjust the tooltip to suit. Name the sheet based on the dimension.

Then duplicate this sheet, and drag the next dimension, eg Segment, and drop it directly on Region. If done properly, everything should seamlessly update. Re-name this sheet accordingly, then repeat the process until you have a sheet for each of the four dimensions.

Applying the proportional brushing

Create a set for each of the relevant dimensions.

Region Set – right click on the Region field in the data pane and select Create > Set. Select all the options to be in the set.

Repeat and do the same for each dimension, so you end up with Segment Set, Ship Mode Set and Sub-Category Set.

We need to determine the combination of all the values selected in each set. So we need

Is Selected Options

[Segment Set] AND [Ship Mode Set] AND [Region Set] AND [Sub-Category Set]

This returns true for all the records in the data which match the combined selections of the individual sets.

On the Region sheet, add Is Selected Options to the Colour shelf. The right click on each set in the data and and select Show Set, so the set of selections are listed on the canvas.

Change the options so only the Segment Consumer and rthe Ship Mode Standard Class are selected, along with all Region and Sub-Category values. Adjust the colours associated to the True and False values that are now presented

If need be, adjust the tooltip so the Is Selected Options is not displaying, then add the Is Selected Options field to the Colour shelf of the Segment, Ship Mode and Sub-Category sheets. Play with the set selections to see how the bars change. Once you’re familiar with the behaviour, reset all the sets so they all contain all the values.

Building the KPI sheet

On a new sheet add Sales to Text. Change the mark type to shape and select a transparent shape (see this blog to get this set up). Adjust the Label to include the text ‘Sales’ and format accordingly. Align middle centre. Add Is Selected Options to the Filter shelf and set to True.

Again, if you adjust the set selections, the value will adjust accordingly.

Building the Dashboard interactivity

Add the sheets onto a dashboard. I used both vertical and horizontal layout containers to get the objects positioned where I wanted. I also used blank objects set to height/width of 1px and with a black background colour to create the horizontal and vertical divider lines. You can see from the item hierarchy in the image below, how I laid out my dashboard (I like to rename my containers to help understanding)

Now add a dashboard change set values action for each of the 4 bar chart sheets.

Select Region

On select of the Region sheet only, target the Region Set. On running the action (ie clicking the bar), assign values to set, and when clearing the selection (clicking the bar again), add all values to the set.

Note – While not specified in the requirements, I noticed that the breadcrumbing functionality in Kyle’s solution didn’t behave if multiple selections of the same dimension were made – eg 2 regions were selected. I decided to add the requirement of only allowing a single dimension to be clicked (ie the single-select only box is checked).

Create a Select Segment, Select Ship Mode and Select Sub-Category set action using the same principals described above.

Creating the breadcrumb

I’ve added this last, so you understand how we can ensure each set only has either all the values in it, or just 1 value.

To create the breadcrumb, we’re going to build up some strings based on what the state of each set looks like. This involved several calculated fields…. I’m not sure if I’ve over complicated this though..

Anyway firstly, we want to capture the values that have been added to each set, so we need

Regions in Set

IF [Region Set] THEN [Region] END

Segments in Set

IF [Segment Set] THEN [Segment] END

Ship Modes in Set

IF [Ship Mode Set] THEN [Ship Mode] END

SubCats in Set

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

The image below shows how each of these fields are behaving based on the set selections – if the value is not selected in the set, the Regions in Set field is Null.

Next we have fields to count how many different values exist in each of these fields.

Count Selected Regions

{FIXED: COUNTD([Regions in Set])}

Count Selected Segments

{FIXED: COUNTD([Segments in Set])}

Count Selected Ship Modes

{FIXED: COUNTD([Ship Modes in Set])}

Count Selected SubCats

{FIXED: COUNTD([SubCats in Set])}

Again you can see from the sheet below, this is counting the number of selections, which is ‘fixed’ (ie the same) for every row.

Now, while this is showing 2, as we’ve manually clicked on the set options, in practice when driven from the dashboard, we’re either going to have all values in the set, or just 1. So based on this assumption, we now just want to get the name of the single selection

Selected Region

IF SUM([Count Selected Regions]) = 1 THEN MAX([Regions in Set]) ELSE ” END

If there’s only 1 item in the set, then get it’s value, otherwise return ‘blank’.

Just testing this behaviour, we can see below that with all the Regions selected, the Selected Region field is empty, but with 1 value selected, we show that value.

Create equivalent fields for each dimension

Selected Segment

IF SUM([Count Selected Segments]) = 1 THEN MAX([Segments in Set]) ELSE ” END

Selected Ship Mode

IF SUM([Count Selected Ship Modes]) = 1 THEN MAX([Ship Modes in Set]) ELSE ” END

Selected SubCat

IF SUM([Count Selected SubCats]) = 1 THEN MAX([SubCats in Set]) ELSE ” END

The order of the dimensions displayed in the breadcrumb is fixed, regardless of the order in which you click the options. That is, if you click a Segment then a Region, the breadcrumb will display the <segment> followed by the <region>. But if you click the Region first and then the Segment, the breadcrumb will still display the<segment> followed by the <region>. Based on this, we can create string values for each dimension that differ depending on whether we know there is a selection made against a subsequent dimension (ie should we include the ‘>’ character or not).

Let’s go through in order. Firstly, no selections made

All Segmentations BC

IF [Selected Segment]=” AND [Selected Ship Mode]=” AND [Selected Region]=” AND [Selected SubCat]=” THEN ‘All Segmentations’ END

If all the ‘selected’ values are empty, then all the sets contain all the values, so display ‘All Segmentations’.

If there are selections made, then the dimensions are ordered as Segment > Ship Mode > Region > Sub-Category

Segment BC

IF [Selected Segment]<>” AND ([Selected Ship Mode]<>” OR [Selected Region]<>” OR [Selected SubCat]<>”) THEN [Selected Segment] + ‘ > ‘
ELSE [Selected Segment]
END

If there is only 1 Segment selected and at least 1 of the other dimensions has been selected too, then add the ‘>’ character after the Segment name, otherwise just show the Segment.

Ship Mode BC

IF [Selected Ship Mode]<>” AND ([Selected Region]<>” OR [Selected SubCat]<>”) THEN [Selected Ship Mode] + ‘ > ‘
ELSE [Selected Ship Mode]
END

Similar to above, but this time, we only need to compare with the dimensions that are below Ship Mode in the display hierarchy.

Region BC

IF [Selected Region]<>” AND [Selected SubCat]<>” THEN [Selected Region] + ‘ > ‘
ELSE [Selected Region]
END

There is only one dimension below Region. As Sub-Category is at the bottom of the ordering, we don’t need anything special – the value of the Selected SubCat field will do.

On a new sheet, add All Segmentations BC, Segment BC, Ship Mode BC, Region BC and Selected SubCat to the Text shelf. Change the mark type to shape and change to use a transparent shape.

Adjust the label, so all the fields are ordered correctly and positioned exactly next to each otherwith no spacing/carriage returns between. Align the label middle left.

Show the set controls, and then test the functionality by altering the selections, ensuring either only 1 value or all values are selected

Once you’ve finished testing, ensure all values are selected in all sets.

The add this sheet to the dashboard – I had the title and the breadcrumb in a vertical container, which was the left hand side of a horizontal container

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

Happy vizzin’!

Donna