For week 24 of #WOW2023, Kyle set this challenge involving dynamic zone visibility to build a mobile friendly KPI visual.

The charts being displayed are relatively simple, and use techniques applied several times in other challenges. Let’s tackle the charts for each ‘page’ one by one.

The Home page

For the Sales KPI

• Double click into Columns and type MIN(1)
• Add Sales to Label.
• Set to Entire View
• Adjust the MIN(1) axis to be fixed from 0-1
• Adjust the text in the Label dialog to include the word Sales and resize fonts.
• Align the label middle centre
• Increase the size to the maximum value
• Adjust the colour
• Format the Sales measure to be $ with 0 dp.
• Uncheck the show tooltip option from the Tooltip dialog
• Hide the Min(1) axis
• Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

Duplicate this sheet and create equivalent ones for Profit and Orders – you’ll need to create a field

Count Orders

COUNTD([Order ID])

for the orders KPI.

For the Sales Sparkline

• Add Sales to Rows
• Add Order Date as a continuous (green) pill set at the Month level.
• Adjust the colour
• Hide both the axis
• Remove all gridlines, zero lines, axis lines, row/column dividers
• Adjust the tooltip
• Format the background of the worksheet to light grey (#f5f5f5).

Duplicate the sheet and create equivalent ones for Profit and Orders.

You should have 6 sheets for the home page.

The Category Page

The Category page is displaying values for the last 12 months based on ‘today’. If building this in a business environment, I would make use of the TODAY() function. But to ensure the viz doesn’t break in future, I’ll hardcode today within a parameter

pToday

date parameter defaulted to 14 June 2023

I then need a field to restrict the records to report over

Last 12 months only

[Order Date]>= DATEADD(‘month’, -12, DATETRUNC(‘month’,[pToday])) AND
[Order Date]<DATEADD(‘month’, 1,DATETRUNC(‘month’,[pToday]))

this will return true if the Order Date associated to the record is greater than or equal to the 1st of the month, 12 months ago, based on the pToday parameter, and the Order Date is less that the 1st of next month, based on pToday.

For the Category KPI sheet

• Add Last 12 months only to the filter shelf and set to true
• Add Category to Rows
• Double click on Columns and type in MIN(1)
• Add Sales and Category to Label
• Set to Entire View
• Adjust the MIN(1) axis to be fixed from 0-1
• Adjust the text in the Label dialog and resize fonts.
• Align the label middle centre
• Increase the size to the maximum value
• Adjust the colour
• Uncheck the show tooltip option from the Tooltip dialog
• Hide the Min(1) axis
• Hide the Category column
• Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

For the Category trend

• Add 12 months only to filter and set to true.
• Add Category and Sales to Rows
• Add Order Date as a continuous (green) pill set at the Month level.
• Adjust the colour
• Add Sales to Label and show the min & max labels for each Category pane
• Hide both the axis
• Remove all gridlines, zero lines, axis lines, column dividers
• Adjust the tooltip
• Format the background of the worksheet to light grey (#f5f5f5).

The Segment Page

Repeat the same steps as described above for the Category Page, but replace any reference to Category with Segment.

Sales by State Page

• Add State/Province to Rows
• Add Sales to Columns
• Sort by Sales descending
• Adjust the Colour
• Click on the Label shelf and Show mark labels
• Remove all gridlines, zero lines, axis lines, row/column dividers
• Adjust the tooltip
• Format the background of the worksheet to light grey (#f5f5f5).

Building the navigator

I went down a slightly longwinded route for this, but its still an acceptable method. I knew deep down it could be done in 1 sheet, but my brain just wasn’t quite wired up properly when I built it.

I basically ended up building 2 sheets per symbol.

Firstly, you’ll need to add the symbol images into your shapes palette.

Create a new field

Selection – Home

“Home”

Also create fields

True

TRUE

and

False

FALSE

• Add Selection – Home to the Text shelf.
• Change the mark type to Shape and select the ‘home’ shape from your custom shape palette.
• Set to Entire View, then adjust the Label alignment to be bottom centre.
• Uncheck the show tooltip option from the Tooltip dialog
• Format the background of the worksheet to medium grey
• Add True and False to the Detail shelf.
• Name the sheet Home – Unselected or similar

Duplicate sheet and change the background colour to teal or similar. Name this sheet Home – Selected or similar.

Repeat the process building 2 sheets for each image – you’ll need to create a Selection– Category field, a Selection – Segment field and a Selection – State field.

Building the calcs for Dynamic Zone Visibility

In order to hide and show various content ‘on click’ we will be making use of dynamic zone visibility. For this we need several boolean fields created along with a parameter

pSelection

string parameter, defaulted to Home

We then need

Is Home Selected

[pSelection] =’Home’

Is Home Not Selected

[pSelection] <>’Home’

Is Category Selected

[pSelection] =’Category’

Is Category Not Selected

[pSelection] <>’Category’

Is Segement Selected

[pSelection] =’Segment’

Is Segement Not Selected

[pSelection] <>’Segment’

Is State Selected

[pSelection] =’State’

Is State Not Selected

[pSelection] <>’State’

Building the Dashboard

We need to make use of multiple (nested) containers in order to get all the content positioned in the right place. I’m not going to go through step by step which containers to place where, but just summarise the key points.

For the ‘navigator’ strip, all 8 sheets need to be placed side by side in a horizontal container, and should be ordered so the ‘home’ sheets are first, then the ‘category’ ones etc. I adjusted the padding around each object to be 1px, and obviously didn’t show the title.

For each sheet, determine whether it should display or not by using the control visibility using value option on the layout tab, and selecting the appropriate field based on which ‘page’ the sheet relates to , and whether it’s the ‘active’ / selected sheet or not.

Eg for the teal Home – Selected sheet, the control visibility using value option should be driven based on the value of the Is Home Selected field, while the grey Home – Unselected sheet should be based on the value of the Is Home Not Selected field.

If all these are set correctly, only 4 of the 8 sheets should be visible at any one time – 1 teal and 3 grey.

For the ‘pages’ ie the set of sheets visible based on the selection in the navigator, a Horizontal Container should be used which in turn consists of 1 vertical container (for the sheets relating to the Home page), 2 horizontal containers (1 containing the 2 sheets side by side for the Category page, and 1 containing the 2 sheets side by side for the Segment page), and finally the Sales by State sheet should be added to the main horizontal container.

The Sales by State sheet should be visible based on the Is State Selected field. Each of the other containers should be visible based on their relevant field.

When putting all this together, the dashboard might look crowded and disorganised, but once the settings have been applied, only 1 page’ should be visible and then you can tweak padding and positioning if need be.

Capturing the selection

We need parameter actions to determine which card should display

Select Home

This parameter action should be applied when the Home – Selected or Home- Unselected sheets are clicked on, and it should set the pSelection parameter, passing in the Selection – Home field.

Equivalent parameter actions should then be created for each of the other Selected/Unselected sheets, passing in the appropriate Selection – xxx field.

Finally to ensure the navigation options don’t remain ‘selected’ on click (the images look darker) we need to apply filter actions to set the true field to false on each of the navigation buttons – this means 8 filter actions, which should look similar to this…

The source sheet selected on the dashboard should target the actual sheet itself (not the one on the dashboard).

Add a title and any other content onto the dashboard. Finally to ensure the viz works properly on a mobile, delete the phone layout option that is automatically listed on the dashboard tab.

My published instance is here. Check out Kyle’s solution to see the 1-sheet navigator.

Happy vizzin’!

Donna