For this week’s challenge, Kyle got us to look at dashboard layout, specially using containers to arrange the charts and KPIs. He added in a sprinkling of interactivity to make the challenge more complete.

The charts aren’t overly complex, so I won’t go into too much detail on building them all out. I used the latest version of Superstore, v2023.1.

Building the KPIs

When I first did this, I built the KPIs on a single sheet, then realised that wouldn’t work to get the layout required, so I ended up with 3 sheets.

• Rename Sales to SALES and format to $ with 0 dp.
• Add Measure Names to Filter and select SALES only.
• Add Measure Names and Measure Values to the Text shelf.
• Align centrally and format the text (I used font size 12pt and 20pt).
• Add Category to the Filter shelf, and select all. Set the filter to apply to worksheets > all using this data source
• Add Order Date to Filter and select Range of Dates. The Order Date pill will be ‘green’. Click on the context menu of the pill and select the Month (May 2015) option, so the range of dates will change at monthly intervals rather than daily. Set the filter to apply to worksheets > all using this data source
• Create a field called True containing the value TRUE and False containing the value FALSE and add both these fields to the Detail shelf. These will be needed later to stop the sheet from remaining highlighted on selection.
• Stop the Tooltip from displaying.
• Name the sheet Sales

Repeat the steps for the Profit Ratio measure – if this doesn’t exist, create the field

PROFIT RATIO

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

and format this to % with 1 dp.

For the Orders measure create

ORDERS

COUNTD([Order ID])

If need be, you can simply duplicate the Sales sheet and just change the Measure Names filter to the appropriate measure.

Creating the Line Chart

Create a parameter to store the name of the selected measure.

pSelectedKPI

string parameter defaulted to the word ‘ORDERS’

Create a field to store the measure to display based on the value of the parameter

Measure to Display

CASE [pSelectedKPI]
WHEN ‘ORDERS’ THEN [ORDERS]
WHEN ‘SALES’ THEN SUM([SALES])
WHEN ‘PROFIT RATIO’ THEN [PROFIT RATIO]
END

On a new sheet, add Order Date to Columns and set to be a green (continuous) month level and Measure to Display to Rows.

I wanted to make my tooltips and labels reflect the measure selected (this isn’t actually part of the challenge). I created

Label – Orders

IF [pSelectedKPI] = ‘ORDERS’ THEN [ORDERS] END

format this to a number with 0 dp.

Label – Profit Ratio

IF [pSelectedKPI] = ‘PROFIT RATIO’ THEN [PROFIT RATIO] END

format this to % with 1 dp

Label – Sales

IF [pSelectedKPI] = ‘SALES’ THEN SUM([SALES]) END

format this to $ with 0 dp.

Add all 3 fields to the Tooltip shelf.

Also create

Chart Label

PROPER([pSelectedKPI])

this is a new function introduced in v2023.1 and will convert ‘ORDERS’ to ‘Orders’ and ‘PROFIT RATIO’ to ‘Profit Ratio’ etc. Add this to Tooltip too.

Modify the Tooltip as below – the 3 Label fields should be directly side by side with no spacing. Only one field will have a value at any time.

Remove the titles from the axes, and remove all gridlines. Name the sheet Line.

Building the Bar Chart

Add Category to Rows and Measure to Display to Columns. Sort descending. Add the 3 Label fields to the Label shelf, and arrange side by side.

Add Chart Label to Tooltip and adjust the tooltip.

Hide the axes, remove all gridlines/axis rulers etc and hide the Category label. Adjust the formatting of the fonts as required. Name the sheet Bar.

Building the dashboard

Describing using layout containers can be quite tricky – objects move around as you place things in. I’m going to do my best to describe the set up/structure I have and hope that it gets you what you need.

My preference is to always start with a floating container sized as per the dashboard. I then add tiled objects into that. I also have a habit of trying to rename my containers in the navigation layout pane to help me find the right section.

So let’s start. Create a new dashboard and set it to to 1200 x 900px.

Click the Floating button at the bottom of the left hand pane, and add a Horizontal container. Set the x & y position to be 0,0 and the width to 1200px and height to 900px. Rename the container to Base.

From the Objects list on the Dashboard pane, click Tiled and add a Text object. Enter the text for the dashboard title. Add a blank object beneath the text object.

When working with containers, it’s always good to add blank objects as a ‘starting point’ These all then get removed.

Now add a vertical container between the Title and Blank object. Name this container Main Body. Add a blank object into that container.

Add a vertical container to the left of the blank object in the Main Body container. Name this container Left Nav. Add a Text object into the Left Nav container and enter the instructional text. Add a blank object above the instructional text.

Add another vertical container to the right of the Left Nav container within the Main Body container. Add the Bar and the Line chart into this container, one above the other. Call this container Charts.

At this point a Tiled container object will have been automatically added containing the parameter.

Leave this for now – we’ll address this shortly.

We can remove some of the blanks now too. Remove the blank within the Main Body container, that is to the right of the Charts container. You can select the object on the item hierarchy layout, right click and remove from Dashboard.

You can also remove the blank object at the bottom of the Base container, below the Main Body container. Your item hierarchy should look something like

Now add another Horizontal container to the charts container, above the bar chart. Add the 3 KPI sheets into this container, position side by side. Name the container KPIs. Select the KPIs container, and use the context menu to Distribute Contents Evenly.

If they haven’t already appeared, the select one of the charts/KPIs and from the context menu select Filters > Category and then Filters > Month of Order Date to get the filter controls visible on the dashboard. They should appear on the right hand side, within that Tiled container (underneath the viz).

From the item hierarchy section, expand the Tiled container until you find the controls listed (see how many containers that got automatically added!).

Select the Category filter (easiest to do this by clicking on the object in the item hierarchy), and then move that object and position it above the blank object in the Left Nav container. Then delete the blank object. Select the Month of Order Date filer object and do the same.

Now we’ve got everything we want , we can remove the Tiled container and all of the objects it contains from the dashboard. Just right click on the first Tiled container in the item hierarchy and Remove From Dashboard (say yes/ok to any prompt that appears). You should have something that looks a bit like this – not the item hierarchy layout.

Now you’ve got everything needed on the dashboard in the right containers, we need to tidy it all up.

• Fix the width of the Left Nav container to around 205 px.
• Change the Category filter to single value dropdown, defaulted to All
• Fix the height of the KPIs container to around 175 px
• Remove the titles from all the KPI objects, and ensure all set to fit entire view
• Amend the titles of the bar and line charts to reference the Chart Label field.
• Fix the height of the bar chart to around 340px and set to fit entire view.

• Set the background colour of the whole dashboard to grey (Format menu -> Dashboard – Dashboard Shading)
• Set the background colour of the bar and line chart to White and add a grey border (slightly darker than the background colour)
• Add borders round the 3 KPI charts too.
• Set padding around all the objects – I tend to use both inner and outer padding. The key is consistency to ensure the spaces between the objects are the same. I typically start with 10 px outer padding all round, and then adjust as required. Sometimes you may add padding to the container and not to the objects themselves, other times you may set the container padding to 0 and apply to the objects, or a combination of both.

Adding the Interactivity

To set the category bar chart to work as a filter, simply select the object and from the context menu select use as filter. Then go to the Actions list (Dashboard > Actions) and edit the ‘Filter 1 (generated)’ action and rename it to something more useful eg Filter bu Category.

For the bar & line chart to update ‘on click’ of a KPI, add a parameter action

Select KPI

On selection of the Orders, Profit Ratio or Sales sheet, set the pSelected KPI parameter, passing the value from the Measure Names field. Keep current value when selection is cleared.

Finally to prevent the KPIs from remaining highlighted in blue on selection, add 3 filter actions (1 per KPI) set up as follows

Deselect Sales KPI

On selection of the Sales sheet on the dashboard, target the Sales sheet directly, and set the fields as Source = True to Target = False.

Finally, to collapse the left hand nav section, select the Left Nav container and from the context menu, select add Show/hide button. A X button will appear which is floating by default. Move this to where you choose (you might need to add some additional left padding to the title to make space.

Load the dashboard in presentation mode to easily test the hide/show functionality.

And hopefully that should be it! Phew!

My published viz is here.

Once you’ve grasped the concept of containers, they really are the best way of controlling the behaviour and layout of objects on your dashboard. When I’m building something formal, I personally never want to have a Tiled container on my dashboard – this is an object that gets automatically added, and you can see from above, how many nested containers it ended up adding through a single action I took. If you’re not careful, you can end up with such a nest of containers, that it can get really hard to unpick.

Happy vizzin’!

Donna