Sean chose to revisit the first challenge he participated in as part of retro-month at WOW HQ. Since the original challenge in 2018, there have been a significant number of developments to the product which makes it simpler to fulfil the requirements. The latest challenge we’re building against is here.

Building the KPIs

This is a simple text display showing the values of the two measures, Sales and Profit. Both fields need to be formatted to $ with 0dp.

• Add Measure Names to Columns
• Add Measure Names to Filter and limit to just Sales and Profit
• Add Measure Values and Measure Names to Text
• Format the text so it is centrally aligned and styled apprpriately
• Uncheck ‘show header’ to hide the column label headings
• Remove row/column dividers
• Uncheck ‘show tooltip’ so it doesn’t display

Building the map

The map needs to display a different measure depending on what is clicked on in the KPIs. We will capture this measure in a parameter

pMeasure

string parameter defaulted to Profit

Then we need to determine the actual measure to use based on this parameter

Measure to Display

If [pMeasure] = ‘Profit’ THEN SUM([Profit]) ELSE SUM([Sales]) END

format this to $ with 0 dp

Double click on State/Province to automatically generate a map with Longitude & Latitude fields. Add Measure to Display to Colour. Adjust Tooltips.

Remove the map background via the map ->background layers menu option, and setting the washout property to 0%. Hide the ‘unknown’ indicator.

Update the title of the sheet and reference the pMeasure parameter, so the title changes depending on what measure is selected.

Show the pMeasure parameter and test typing in Sales or Profit and see how the map changes

Building the bar chart

Add Sub-Category to Rows and Measure to Display to Columns. Sort descending. Adjust the tooltip.

Edit the axis so the title references the value from the pMeasure parameter, and also update the sheet title to be similar.

Building the dimension selector control

The simplest way of creating this type of control is to use a parameter containing the values ‘State’ and ‘Sub-Category’. But you are very limited as to how the parameter UI looks.

So instead, we need to be build something bespoke.

As we don’t have a field which contains values ‘State’ and ‘Sub-Category’, we’re going to use another field that is in the data set, but isn’t relevant to the rest of the dashboard, and alias some of it’s values. In this instance I’m using Region.

Right click on the Region field in the data pane and select Aliases. Alias Central -> State and East -> Sub-Category.

On a new sheet add Region to Rows and also to Filter and filter to State & Sub-Category. Manually type in MIN(0.0) into the Columns shelf. Add Region to the Label shelf and align right. Edit the axis to be fixed from -0.05 to 1, so the marks are shifted to the left of the display.

We will need to capture the ‘dimension’ selected, and we’ll store this in a parameter

pDimension

string parameter defaulted to Central

(note – although the fields are aliased, this is just for display – the values passed around are still the underlying core values).

To know capture which dimension has been set we need

State is Selected

[Region] = [pDimension]

Change the mark type to Shape and add State is Selected to the Shape shelf, adjusting so ‘true ‘ is represented by a filled circle, and ‘false’ by open circle. Set the colour to dark grey.

Change the background colour to grey, amend the text style, hide the Region column and the axis, remove all gridlines/row dividers.

Finally, we will need to stop the field from being ‘highlighted’ on selection. So create two fields

True

TRUE

False

FALSE

and add both of these to the Detail shelf. We’ll apply the required interactivity later.

Building the dashboard

You will need to make use of containers in order to build this dashboard. I use a vertical container as a ‘base’ which consists of the rows showing the title, then BANs, a horizontal container for the main body, and a footer horizontal container.

In the central horizontal container, the map and the bar chart should be displayed side by side. We need each to disappear depending on the dimension selected. For this we need

Show Map

[pDimension] = ‘Central’

and

Show Bar

[pDimension] = ‘East’

On the dashboard, select the Map object and then from the Layout tab, select the control visibility using value checkbox and select the Show Map field.

Do the same for the Bar chart but select the Show Bar field instead.

Select the colour legend that should be displayed and make it a floating object. Position where you want, and also use the Show Map field to select the control visibility using value checkbox.

Adding the interactivity

To select the different measure on click of the KPI, we need a parameter action

Set Measure

On select of the KPI chart, set the pMeasure parameter passing in the value from the Measure Names field.

And to select the dimension to allow the charts to be swapped, another parameter action

Set Dimension

On select of the Dimension Selector sheet, set the pDimension parameter, passing in the value from the Region field

Finally, to ensure the dimension selector sheet doesn’t stay ‘highlighted’, add a filter action

Unhighlight Dimension Selector

On select of the Dimension Selector sheet on the dashboard, target the Dimension Selector sheet directly, and pass values setting True = False

Hopefully this is everything you need to get the dashboard functioning. My published viz is here.

Happy vizzin’!

Donna