Sean based this week’s challenge on a scenario he’d faced with a client and requires the use of dynamic zone visibility (DMZ) and filter actions.
I’m going to attempt to be relatively brief for the vizzes/views that need building, as the main focus is on the dashboard itself.
Creating the Main KPIs
Connect to the data source and add a data source filter on Country/Region = United States so any data related to Canada gets excluded automatically from everything we build.
Create a new field
Profit Ratio
SUM([Profit])/SUM([Sales])
and format to % to 2 dp
On to a new sheet add Sales, Profit, Profit Ratio and Quantity onto the canvas. I just double-clicked each field and once all added used Show Me to display a Text table.
Move Measure Names to Columns and re-order the measures. Add Measure Names to Text too and modify the text so the Measure Names label is beneath Measure Values, aligned centrally and formatted as required. Set the sheet to Entire View. Format the Sales and Profit pills on this sheet to be formatted to $k to 1 dp.
The KPIs need to filter based on the state that will be selected in the map. We’re going to use a parameter to capture the state
pSelectedState
string parameter defaulted to <empty string?
And then we create
Is Selected State?
[pSelectedState]=” OR [State/Province] = [pSelectedState]
Add this to the Filter shelf and select True.
Format the sheet to remove row/column dividers and hide the Measure Names label header (uncheck show header on the pill).
Name the sheet KPIs-Main or similar.
Creating the Customer KPIs
Although not actually working on Sean’s solution, I believe the intention of showing KPIs on the ‘customer panel’ is to give a summary based on the selected customer. So to do this simply duplicate KPIs-Main and then add Customer Name to the Filter shelf and select all customers. Name the sheet KPIs-Customer or similar.
Creating the Map
ON a new sheet, double click State/Province to automatically create a map and then add Sales to Colour. (if the map doesn’t display the US, then you may need to edit locations – Map > Edit Locations). Adjust the background of the map and remove all city/state labels (Map > Background Layers). Name the sheet Map or similar.
Creating the Customer Orders list
On a new sheet, add Product Name to Rows and then Sales and Quantity as a table. As Is Selected State to Filter and set to True and also add Customer Name to Filter and select all values. Ultimately on click of a state in the map, this list needs to be filtered to just the orders associated to that state , and then additionally be able to be filtered by customer name
Creating the Reset Button
On a new sheet, double click into the space below the marks card and type ‘Reset’ to create a ‘dynamic’ pill. Then change the mark type to square and add this pill onto the Label shelf. Set the size to the largest possible and set the sheet to entire view. Format the label to be centred and larger. Then create a field
State-Reset
”
and add this onto the Detail shelf. This field basically contains an empty string, and we’ll use it later to ‘reset’ the pSelecedState parameter. Call the sheet Reset Button or similar.
Building the dashboard & interactivity
Using layout containers, build out the dashboard as required. I used a horizontal container in the middle of the dashboard which displayed the Map on the left and another Vertical Container on the right. The vertical container is the ‘customer panel’ and it then contained the Customer KPIs, and the Customer Orders sheets along with the Customer Name quick filter (set to a single value dropdown) and reset button in their own horizontal container. My hierarchy is shown below.
Add a dashboard parameter action to select a state
Set State
On select of the Map sheet, set the pSelectedState parameter with the value from the State/Province field. But on clearing the selected keep the current value.
Clicking on a State will now change the KPIs and the list of customer orders
But we also want the list of customers in the filter list to be restricted to those who have orders in the state, so adjust the quick filter settings so that it is set to only relevant values Also customise the control to so the ‘all’ option is not displayed.
Test this behaviour by selecting different States and then checking the values in the Customer Name filter control.
When the Reset button is clicked, we want the State to be deselected and the KPI values all reset, so create another parameter action
Reset State
On select of the Reset Button sheet, set the pSelectedState parameter with the value from the State-Reset field
If you test this out without selecting a Customer Name filter, then the behaviour will work as expected: click a state, KPIs/Orders List and set of customers in the filter change; click reset and KPIs/Orders List and set of customers in the filter all reset .
However if after clicking a state, a customer is then selected in the filter (which filters the Orders List and the Customer KPIs)
If I then click the rest button, the customer panel now shows all the info for the selected customer
and if I now select another state, the customer filter is retained, and if that customer has no orders for the state, I don’t get what I expect
We need to be able to essentially ‘reset’ the customer filter as well when clicking the reset button.
This was the trickiest part of this challenge, and I did try a variety of other ways of displaying the customer filter to see if I could crack it, but I did observe through playing with Sean’s solution and hovering the mouse, that the Customer Name filter was a standard quick filter. Eventually, with the clue being in the challenge title, I got there.
Add a dashboard filter action
Reset Customer Filter
On select of the reset button, target the Customer Orders, KPIs Customer but filter on selected fields where Customer Name = Customer Name
Finally we want the customer panel to only display once the map is clicked. For this, create a new field
State parameter is not empty?
[pSelectedState] <> ”
which will be true when pSelectedState contains the name of a state which happens ‘on click’ of the map. Then back on the dashboard, select the ‘customer panel’ container and on the Layout section, check the control visibility using value option and select the above field
And fingers crossed, you should now have a functioning viz. My published viz is here.
For this week’s challenge, Yusuke asked us to provide a solution to allow charts to be coloured by different dimension, but he sprinkled a few extras in just for good measure 🙂
Defining the parameter
The key driver here is going to be the use of a parameter to define the dimension we need to colour by.
pColourBy
string parameter defaulted to Order Date, listing the 4 options as below
We then need a field that uses this parameter to define the actual dimension we’ll colour by
Colour
CASE [pColourBy] WHEN ‘Order Date’ THEN STR(YEAR([Order Date])) WHEN ‘Region’ THEN [Region] WHEN ‘Category’ THEN [Category] WHEN ‘Segment’ THEN [Segment] END
Building the Order Date chart
On a new sheet, add Order Date to Columns and Sales to Rows. Change the mark type to Bar and add Colour to the Colour shelf. Adjust the colours to suit, set the opacity to 70% and add a white border. Show the pColourBy parameter.
Change the options in the pColourBy parameter and each time readjust the colours as you wish.
Add a reference line to the Sales axis that displays the value of TotalSales per cell
Format the reference line to format the displayed number in $M and bold font, and align top middle.
Create a new field
% of Sales
IF SUM([Sales]) / TOTAL(SUM([Sales])) <> 1 THEN SUM([Sales]) / TOTAL(SUM([Sales])) END
and format to % to 1dp. This will only display a value if its not 100%.
Add this to the Label. Adjust the table calculation setting so it is computing by the Colour field only.
Adjust the Label so the font is bold and the label only appears when Highlighted. Then update the Tooltip as required.
Although not explicitly called out in the requirements, I noted that if Yusuke clicked on the chart title, it reset the dimension to colour by. To deal with this we need to create
param Order Date
‘Order Date’
Add this to the Detail shelf.
We also need to ‘fake’ the title to be part of the chart itself (so it’s clickable). Double click into the Columns and manually type ‘Sales by Order Date’ and position the pill created before Order Date.
Right click on the column label (the text in darker font) and hide field labels for columns. Then right click on the column label to format – set the font to 12pt and bold, align left and shade the background to light grey. Increase the width of the column heading.
Then right click on the corner whitespace next to the heading just created, and format. Apply a light grey shading to the corner too.
If the ‘title’ is clicked, we don’t want it to be ‘highlighted’/’selected’. For this we will need fields
True
TRUE
False
FALSE
Add both of these to the Detail shelf.
Finally tidy up by removing the axis title, adjusting the font of the axis labels (I made them a bit darker), and removing row & column dividers. Name the sheet Order Date or similar.
Building the Region chart
Duplicate the Order Date chart and then click the option in the menu to swap axis so we have a horizontal bar chart.
Move the ‘Sales by Order Date’ pill from Rows to Columns and update the text to become ‘Sales by Region’ instead. Drag the Region pill and drop it directly over the Order Date pill on the Rows so it replaces it and all references to the field are replaced too. Widen the rows.
Right click on the ‘Region’ text in the column heading and hide field labels for rows. Format the reference line to align middle right.
Create a new field
param Region
‘Region’
and add this to the Detail shelf instead of the param Order Date field. Name the sheet Region or similar
Building the Category Chart
Duplicate the Region chart, and go through similar steps described above so the ‘title’ is Sales by Category and a new field
param Category
‘Category’
replaces param Region on the Detail shelf.
Building the Segment Chart
Repeat as above, this time setting the ‘title’ to Sales by Segment and a new field
param Segment
‘Segment’
replaces param Region on the Detail shelf.
Adding the interactivity
Add the sheets to a dashboard using layout containers and padding to organise as required. Then create the following dashboard actions
Highlight Action :Highlight
On hover of any of the charts on the dashboard, target all other charts, highlighting based on the Colour field only.
This action makes all the % labels appear when the mouse cursor is moved over the bars.
Parameter Action : Set Order Date Param
On Select of the Order Date sheet, set the pColourBy parameter with the value from the param Order Date field.
Parameter Action : Set Region Param
On Select of the Region sheet, set the pColourBy parameter with the value from the param Region field.
Parameter Action : Set Category Param
On Select of the Category sheet, set the pColourBy parameter with the value from the param Category field.
Parameter Action : Set Segment Param
On Select of the Segment sheet, set the pColourBy parameter with the value from the param Segment field.
These actions change the value displayed in the pColourBy parameter when the ‘title’ of the charts is clicked on.
Filter Action: Deselect Order Date Title
On select of the Order Date sheet on the dashboard, target the Order Date worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
Filter Action: Deselect Region Title
On select of the Region sheet on the dashboard, target the Region worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
Filter Action: Deselect Category Title
On select of the Category sheet on the dashboard, target the Categoryworksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
Filter Action: Deselect Segment Title
On select of the Segment sheet on the dashboard, target the Segment worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
And once these have all been applied, you should have a functioning dashboard. My published version is here.
Sean set this week’s challenge to give an alternative solution to displaying a table of details rather than the traditional ‘pancake table’ (his words not mine 🙂 ).
The main crux of the challenge relates to the dashboard actions and interactivity, so I’ll be brief(ish) in describing how to build the charts.
Creating the line chart
Add Order Date to Columns at the month-yearcontinuous (green pill) level. Add Sales to Rows. Format Sales to $ with 0 dp. Remove the title on the Order Date axis. Update the Tooltip to give an instruction to ‘click the line to filter’. Rename the sheet Sales Trend or similar.
Creating the bar chart
Add Sub-Category to Rows and Sales to Columns. Sort by Sales descending. Hide the Sub-Category row heading label (right click > hide field labels for rows). Update the Tooltip to give an instruction to ‘click the bar to filter’. Rename the sheet Sales by Sub Bar or similar.
Creating the Tree Map
Add Segment and Ship Mode to Detail and Sales to Size. Move Segment to Colour and reduce opacity to about 60%. Move Ship Mode to Label and then add additional Segment and Sales pills to Label. Add a table calculation against the Sales pill on the Label shelf, so it is applying a percentage of Total by Segment only.
Add another instance of the Sales pill to Label and then update the layout of the label.
Move the Segment pills on the marks shelf so they are positioned below the Ship Mode to ensure the tree map is segmented based on the Ship Mode (there should be four blocks divided by the thicker white lines).
Update the Tooltip to give an instruction to ‘click the treemap to filter’. Rename the sheet Treemap or similar.
Build the Details table
On a new sheet add Order ID, Customer Name, Order Date (as a discrete exact date – blue pill), Ship Date(as a discrete exact date – blue pill) and Product Name to Rows. Add Sales to Text. Format Profit to $ with 0 dp and drag onto the canvas over the columns of Sales numbers, and release the mouse when the Show Me option appears. Add Discount into the Measure Values section. Change the aggregation to Average and then format to be % to 0 dp. Rearrange the order of the pills in the Measure Values section as required. Add Segment, Sub-Category and Ship Mode to the Detail shelf. Update the title to reference these 3 pills. Hide the Tooltip. Rename the sheet Details or similar.
Building the additional calculations needed
In clicking around Sean’s solution, I was finding what I had initially built wasn’t quite doing what Sean did. If I clicked on the bar chart and then the tree map, the details were only filtered based on the tree map and vice versa. There were ways to solve this, but this then resulted in other issues, in that after closing the details table, the charts remained filtered, but it wasn’t obvious as nothing was highlighted. Basically what I’m trying to say, is the filtering seemed like it should be straightfoward, but wasn’t. I ended up using a combination of parameters and filter actions.
So we’ll start by dealing with the parameters we need.
Create the following parameters
pSelectedDate
date parameter defaulted to 01 Jan 1900
pSelectedSegment
string parameter defaulted to <emptystring>
pSelectedShipMode
string parameter defaulted to <emptystring>
pSelectedSubCat
string parameter defaulted to <emptystring>
Then create the following calculated fields
Filter: Date
[pSelectedDate] = #1900-01-01# OR [pSelectedDate]=DATETRUNC(‘month’,[Order Date])
add this to the Filter shelf on the bar chart, tree map and details sheets and set to True.
Filter: SubCat
[pSelectedSubCat]=” OR [pSelectedSubCat]=[Sub-Category]
add this to the filter shelf on the line chart, tree map and details sheets and set to True
Filter: Segment
[pSelectedSegment]=” OR [pSelectedSegment]=[Segment]
add this to the filter shelf on the line chart, bar chart and details sheets and set to True
Filter: Ship Mode
add this to the filter shelf on the line chart, bar chart and details sheets and set to True
We also need a parameter to capture when we want to show the details table.
pClickMade
boolean parameter defaulted to False.
and to supplement it, we need a calculated field to use to set this parameter to true
Click Made
TRUE
Add Click Made to the Detail shelf of the line chart, bar chart and tree map.
We’ll set these parameters later.
Building the Close icon
The ‘close’ cross when the details sheet is displayed is another sheet. On clicking on it, we will want to set the pClickMade parameter to False so the Details will no longer show. For this we will need
Close
FALSE
Add this field to the Detail shelf on a new sheet. Change the mark type to shape and change the shape to a X. Set the colour to black and set to fit entire view. Hide the Tooltip. Name the sheet Close or similar.
Building the dashboard and interactivity
Using layout containers, arrange the line chart, bar chart and tree map into a dashboard. Use padding and background colours to get the layout as desired.
The add the Details sheet as a floating object and position over the top of the other charts. Set the background to white and add a black border. Also float the Close sheet into position too. Hide the title and also add a black border.
Select the Close sheet object, and then from the Layout tab in the left hand nav, check the Control visibility using value checkbox and select the pClickMade parameter
It should disappear if the parameter is still set to false. Repeat the same process with the Detail sheet object.
Now create the following dashboard parameter actions
Filter Month
On select of the Sales Trend sheet, target the pSelectedDate parameter, passing in the value from the Order Date. When the selection is cleared, reset to 01 Jan 1900.
Filter SubCat
On select of the Sales by Sub Bar sheet, target the pSelectedSubCat parameter, passing in the value from the Sub-Category. When the selection is cleared, reset to <emptystring>.
Filter Ship Mode
On select of the Treemap sheet, target the pSelectedShipMode parameter, passing in the value from the Ship Mode. When the selection is cleared, reset to <emptystring>.
Filter Segment
On select of the Treemap sheet, target the pSelectedSegment parameter, passing in the value from the Segment. When the selection is cleared, reset to <emptystring>.
Drill to Details
Via the menu of the Sales by Sub Bar, Sales Trend, and Treemap sheets, target the pClickMade parameter passing in the value from the Click Made field. When the selection is cleared, set the value to False.
Close Details
On select of the Close sheet, target the pClickMade parameter, passing in the value from the Close field. When the selection is cleared, keep the value.
If you start clicking around, you should find that all these actions do provide some level of filtering, but if you for example, click on the bar (to filter the line and treemap), and then click on a section in the tree map and use the ‘Drill down to details’ menu option, the details table has lost the filtering of the bar chart as the bar has become unselected when the treemap chart was clicked.
To resolve this, apply filter actions to the line chart, bar chart and tree map objects (the quickest way to do this is just select the object on the dashboard and click the ‘filter’ icon in the context menu.
If you do this on all 3 sheets and then look at the list of dashboard actions you’ll see 3 ‘Filter x (generated)’ entries.
By applying this mix of filtering through ‘default’ dashboard filter actions in conjunction with parameters, I think you have a more complete and understandable experience. And you will have to explicitly unselect each of the marks you clicked on to remove that filter. I added instructions on the dashboard to aid with this.
This week’s #WOW2025 challenge was set live as part of TC25. Unfortunately, this year I couldn’t be there in person to meet everyone, which for the last 3 years has been my conference highlight 😦
Anyway, Kyle set the challenge, and conscious of time, provided a starting workbook, so the focus could be on the container and DZV functionality. For those who nailed this, he added some additional interactivity with dashboard actions.
So the first thing is to download the starter workbook from the challenge page.
I’m going to attempt to build this in the order of Kyle’s requirements.
Layout out the dashboard
So the requirement states that no floating objects are allowed. Typically when I build a dashboard for business purposes or where the layout is a little complicated, I always start by adding a floating container sized to the exact dashboard size and positioned 0,0. I then add tiled objects into it. Doing this means I don’t end up with Tiled container objects on my dashboard (or if any get added when legends/filters get automatically added, I just move any items I want to retain and then delete the Tiled container).
However, as Kyle says ‘no floating’, I will build adding to the ‘default’ dashboard which means there will be containers on there I don’t really want.
Now blogging about containers is usually very tricky as it’s hard to explain where things need to go. So I’ll be supplementing this with a lot of screen shots – fingers crossed following along works out ok!
To start, create a dashboard sheet and resize to 1200 x 900 as required. Observe the item hierarchy section of the Layout pane as this is where you’ll see all the containers and objects as we add them to the dashboard.
The main structure of the display is split into 2 columns, so start by adding a horizontal layout container to the dashboard. Once added, add 2 blank objects side by side to give the basic layout. Adding blank objects helps when positioning the required objects and is recommended when dealing with layout containers, especially if you’re new to them. They will ultimately be deleted as we go. Rename the horizontal container H – 2 cols or similar (right click on the container in the item hierarchy > rename).
Notice how a Tiled container has now also appeared on the dashboard, even though we only added a horizontal container.
The first column of the dashboard contains 2 charts – the Scatterplot and the Sales & Profit Ratio Comparison sheets – stacked on top of each other. For this, add a vertical layout container between the two blank objects. Rename this V – Col 1.
Add the Scatterplot sheet into the vertical container and then add Sales & Profit Comparison underneath it.
The various legends associated with these 2 sheets, automatically get added into their own vertical container on the right hand side. These aren’t required, so from the item hierarchy, select the Vertical container and then Remove from dashboard.
The right hand column of the display will show the Sales & Profit Ratio by Month sheet and another (hidden) chart that needs to be built.
Add another vertical container between the V – Col 1 container and the right hand blank object. Name this V – Col 2, and add the Sales & Profit Ratio by Month sheet and then another blank object underneath it. Once again remove the right hand vertical container that is automatically added with all the legends/filters.
Now we have the ‘core’ layout, the 2 blank objects we added to the horizontal container, H – 2 Cols, right at the start, can be removed, so hopefully you should have a layout organised as below.
Now add the dashboard title (Dashboard menu > Show Title, and then update the text). This will automatically add a vertical layout container around all the existing contents.
Building the Sales & Profit Ratio by Sub-Category bar chart
On a new sheet, add Sub-Category to Rows and Sales to Columns. Add Profit Ratio to Colour and adjust the colour legend to use the Red-Black Diverging colour palette. Hide the Sub-Category row label heading (right click > hide field labels for rows).
The bar chart needs to be filtered when a State in the Sales & Profit Ratio Comparison chart is clicked on, or when a Date is selected in the Sales & Profit Ratio by Month chart. However, I noticed when clicking around, that when clicking the Sales & Profit Ratio by Month chart, it filtered the above bar chart by both the State and Date. So based on this, create 3 parameters.
S&PR Comp State
String parameter defaulted to empty string
S&PR by Month State
String parameter defaulted to empty string
S&PR by Month Date
Date parameter defaulted to 01 Jan 1900 (essentially a null date)
Show these parameters on the sheet.
We want to filter the chart if the S&PR Comp State has a value and the S&PR by Month Date is the ‘null’ date (which means we’ve interacted with the Sales & Profit Ratio Comparison chart), or if the S&PR Monthly State has a value AND the S&PR by Month Date has a value (which means we’ve interacted with the Sales & Profit Ratio by Month chart). So create
Filter – S&PR by SubCat
([State Name] = [S&PR Comp State] AND ([S&PR by Month Date]=#1900-01-01#))
OR
(([State Name] = [S&PR by Month State]) AND (DATETRUNC(‘month’, [Order Date]) = DATETRUNC(‘month’, [S&PR by Month Date])))
Enter a State name into the S&PR Comp State parameter (eg New Jersey), then add the Filter – S&PR by SubCat field to the Filter shelf and set to True. The chart should change.
Verify the functionality by adding a state and date into the other parameters eg 01 March 2021 and Texas
Empty the state parameters and set the date back to 01 Jan 1900. Name the sheet Sales & Profit Ratio by SubCat. The chart contents will disappear.
Creating a dynamic title sheet
Originally I hoped to do this without using another sheet and just using the title of the bar chart, but I need the date to show nothing rather than Jan 1900 depending on the user interactivity, so a new sheet is required.
But for it, we need some additional calculated fields.
State for Title
IIF([S&PR by Month State]<>”,[S&PR by Month State], [S&PR Comp State])
We only want to show the name of the state once, and both parameters may have it set.
Date for Title
IF [S&PR by Month Date]=#1900-01-01# THEN ” ELSE DATENAME(‘month’,[S&PR by Month Date]) + ‘ ‘ + STR(YEAR([S&PR by Month Date])) END
Line
IF [S&PR by Month Date]<>#1900-01-01# THEN ‘|’ ELSE ” END
Add all 3 fields to the Detail shelf of a new sheet. Change the mark type to polygon. Update the sheet title as below
Name the sheet S&PR Title Sheet or similar
Adding the bar chart, title & legend to the dashboard
All 3 of these objects – the bar chart, the title sheet and the profit ratio legend need to show or hide based on interactivity. To do this in one step, we can encapsulate the 3 objects within containers within another ‘parent’ container and control the visibility on the ‘parent’ container.
Add a vertical container between the Sales & Profit Ratio by Month chart and the blank object. Name this V – S&PR SubCat Chart
Add the Sales & Profit Ratio by SubCat sheet into this. Then add another horizontal container and place it above the Sales & Profit Ratio by Sub Cat chart (making sure it’s within the V – S&PR Sub Cat Chart container. Rename this H – S&PR Sub Cat Title.
Add the S&PR by Title sheet into this horizontal container, and then click on the Profit Ratiolegend on the right hand side and move this object to sit to the right of the title sheet. Then click on the right hand column containing all the remaining legends, and delete this container from the dashboard. Then remove the blank object that’s sitting beneath the Sales & Profit Ratio by SubCat sheet. You should have something like below…
Adjust the width of the S&PR Title sheet so its wider. Set the sheet to Fit Entire View. Then select the H – S&PR SubCat Title container and edit the height to be 90 px.
Hide the title of the Sales &Profit Ratio by SubCat sheet.
Hiding and showing the Sales & Proft Ratio by Sub Category section
Create a new calculated field
Show S&PR by Sub Cat
[S&PR by Month State]<>” OR [S&PR Comp State]<>”
On the dashboard, select the V – S&PR SubCat Chart container and on the Layout pane, check the Control visibility using value checkbox, and select the Show S&PR by Sub Cat field. Assuming all the parameters are set to their default values, then the whole section should disappear, although the container will still be selected.
To make the section show, we need to set the parameters using dashboardparameter actions.
Set S&PR Comp State
On select of the Sales & Profit Ratio Comparison sheet, set the S&PR Comp State parameter passing in the value of the State Name field. When the selection is cleared, set the value back to <emptysrting>
Click on a row in the Sales & Profit Ratio Comparison bar chart, and the Sales & Profit Ratio by SubCat chart should display, filtered to that State, with the selected state name in the title.
Click the state again, and the chart disappears.
Create 2 further dashboard parameter actions
Set S&PR by Month State
On select of the Sales & Profit Ratio by Month sheet, set the S&PR by Month State parameter, passing in the value from the State Name field. When the selection is cleared, set it back to <emptystring>
Set S&PR by Month Date
On select of the Sales & Profit Ratio by Month sheet, set the S&PR by Month Date parameter, passing in the value from the Month([Order Date]) field. When the selection is cleared, set it back to 01/01/1900
Now click on a point in the line chart, and the Sales & Profit Ratio by SubCat chart should display filtered to the relevant state and month
Adding the Additional Interactivity
When the Scatterplot is clicked, the State in the existing ScatterplotState parameter should be updated. Create a dashboard parameter action
Set Scatterplot State
On select of the Scatterplot sheet, set the Scatterplot State parameter, passing in the value from the State field. When the selection is cleared, retain the value
If you click around the scatterplot, the Sales & Profit Ratio by Month line chart and Sales & Profit Ratio Comparison charts should update.
But we don’t want the other marks on the scatter plot to ‘fade’. To solve this, create a dashboard filter action.
Deselect Scatter marks
On select of the Scatterplot sheet on the dashboard, target the Scatterplot sheet directly, setting the fields TRUE = FALSE. On clearing the selection, show all values.
Finally, the last requirement is to highlight the line in the Sales & Profit Ratio by Month chart associated to the State selected in the Sales & Profit Ratio Comparison chart. For this first create a dashboard set action to capture the selected state
Add State to Set
On select of the Sales & Profit Ratio Comparison sheet, target the State Name Set. Check the single-select only checkbox. Running the action should Assign value to set and clearing the selection should remove all values from set
Then add a dashboard highlight action
Highlight Monthly Trend Chart
On select of the Sales & Profit Ration Comparison sheet, target the Sales & Profit Ratio by Month sheet targeting the State Name field only
And hopefully, with all this, you should have a fully interactive dashboard. My published viz is here.
Erica set the latest challenge, testing us on our ability to master tricky filter scenarios – in this case either show the info for one specific value of a field, or only show the other values, but allow them to be filtered themselves too. The challenge had two parts – the main challenge and a bonus option. I managed to complete both, so will blog both too.
Main challenge – Building the basic viz
On a new sheet add Region and Category to Rows and Sales to Columns. Add Region to Colour and adjust accordingly.
Sort Region by Sales descending
and then click the descended sort button on the toolbar to sort the Category field by Sales too.
Format Sales to be $ with 0 dp. Remove column dividers, and widen each row slightly.
Main challenge – Apply the filtering
Create a parameter
pRegionType
string parameter with 2 options : Not West and West, defaulted to Not West
Create a calculated field to determine whether to display the West Region only, or the other Regions
Filter Region West or Not v1
([pRegionType] = ‘West’ AND [Region] = ‘West’) OR ([pRegionType] = ‘Not West’ AND [Region] <> ‘West’)
Add this to the Filter shelf and set to True. This is essentially the ‘first level’ filter. Show the parameter and switch between the two values to see the behaviour
Now we need a ‘second’ filter, to allow the relevant Regions to be selected. For this, add Region to the Filter shelf, but select the Use all option
and then show the Region filter list on the canvas, and adjust the settings so only relevant values are displayed
This means when the pRegionType parameter is West, only West will be displayed in the Region filter, but when Not West is selected, all regions except West will display, and the filter can be interacted with in the normal manner.
Main challenge – Building the dashboard
Arrange the viz and the parameters on the dashboard as required, using layout containers, padding and background colours to help organise the content and display required.
We only want the Region selection filter to display when the pRegionType parameter is set to Not West. We can use dynamic zone visibility for this. Create a calculated field
DMZ – Display Filter Control
[pRegionType] = ‘Not West’
and then on the dashboard, select the Region filter and check the Control visibility using value option and select the DMZ – Display Filter Control field.
Bonus Challenge – Building the Viz
Recreate the viz as described above (or duplicate the sheet of the original viz, and remove all the pills from the Filter shelf.
Bonus challenge – Apply the filtering
Create a parameter
pSelectedRegion
string parameter, defaulted to <empty string>
This parameter is going to contain a string that can contain one or more Regions in a delimited format eg | East | or |East||South| etc. The contents of this string will determine how we filter the chart to mimic the required behaviour.
Firstly, we want the ‘1st level’ filter to determine whether we’re displaying just the West Region or all the other Regions.
Filter Region West or Not v2
(CONTAINS([pSelectedRegion],’West’) AND [Region] = ‘West’) OR (NOT CONTAINS([pSelectedRegion], ‘West’) AND [Region] <> ‘West’)
Add this to the Filter shelf and set to True. Show the pSelectedRegion parameter. With the parameter empty, the WestRegion should not display.
Type the word West into the parameter. Now the just the West Region should display.
And if you enter additional text alongside the word ‘West’, still the ‘West’ Region should display
But if you remove the ‘West’ text, all the Regions should display whatever the text is contained.
This behaviour is essentially simulating that of the ‘West’ | ‘Not West’ parameter selection in the previous version.
Now we want to control the 2nd level of filtering where the same parameter is used to drive which of the ‘other’ Regions display.
Filter Other Regions v2
CONTAINS([pSelectedRegion], ‘West’) OR NOT CONTAINS([pSelectedRegion],[Region])
Set the pSelectedRegion parameter to empty so all Regions are displayed. Add Filter Other Regions v2 to the Filter shelf and set to True.
Enter the text East into the parameter. The East option should disappear.
Add the text ‘South’. That too should disappear
Add the text ‘West’ and only the West Region will show
Play around entering multiple combinations of Regions. Ultimately if the text ‘West’ is present anywhere in the parameter string, only the West Region will display. If West is not present, then any other Region in the string will not be presented in the display. All sounds a bit backwards, but it works 🙂
So now we need to actually control how the pSelectedRegion parameter will get populated. And this will be via a parameter action fired from the selection made from a ‘custom’ legend sheet.
Bonus challenge – Building the filter control
On a new sheet, add Region to Rows and manually type in MIN(0.0) into Columns. Change the mark type to shape. Add Region to Label and show the labels (widen each row slightly). Edit the MIN(0.0) axis to be fixed from -0.1 to 0.5 which will shift the display to the left.
Sort the Region field by Sales descending.
Hide the axis, stop the Tooltip from displaying, hide the Region header, remove all gridlines/ axis rulers/ zero lines, row/column dividers. Set the background colour to light grey.
The Colour and the Shape (filled or unfilled) is determined based on the entries we have captured in the pSelectedRegion parameter, but the logic for each attribute is different.
Colour v2
If [pSelectedRegion] = ‘|West|’ THEN ‘West’ ELSE [Region] END
Show that parameter and make it empty. Add Colour v2 to the Colour shelf. Adjust colour to suit if not already set.
Then enter the text |West| – all the symbols should now all be Navy (or whatever colour you have chosen for West).
For the shape, create
Shape v2
IF CONTAINS([pSelectedRegion] , ‘West’) AND [Region] = ‘West’ THEN ‘Fill’ ELSEIF CONTAINS([pSelectedRegion], ‘West’) AND [Region] <> ‘West’ THEN ‘Empty’ ELSEIF ([Region] <> ‘West’) AND [pSelectedRegion]=” THEN ‘Fill’ ELSEIF ([Region] <> ‘West’) AND NOT CONTAINS([pSelectedRegion],[Region]) THEN ‘Fill’ ELSE ‘Empty’ END
and add to the Shape shelf. Note – this logic took a lot of trial and error to get the desired result.
Whenever the text West exists in the parameter, then the West Region should be a filled circle and all the other regions should be empty (the first 2 lines of the logic statement). If the parameter is empty, we want all the regions (except West) to be filled (so West will be empty). And if the parameter contains a Region(s) that isn’t West, we want that Region to be empty as well – only non-West Regions that aren’t in the parameter should be filled.
To control the text being passed into the pSelectedRegion parameter, we need a field
Region for Param
IF CONTAINS([pSelectedRegion],’West’) THEN ” //West has been selected again so reset parameter to empty ELSEIF CONTAINS([pSelectedRegion], [Region]) THEN REPLACE([pSelectedRegion], ‘|’ + [Region] + ‘|’ ,”) //selected region is already in the parameter, so remove it ” ELSE [pSelectedRegion]+ ‘|’ + [Region] + ‘|’ //append current region selected to the existing parameter string END
Add this to the Detail shelf.
Finally, we will want to ensure the marks aren’t highlighted on selection, so create fields
True
TRUE
False
FALSE
and add these to the Detail shelf too.
Bonus challenge – adding the interactivity
Build the dashboard again using layout containers and background colours and padding
Create a dashboard parameter action
Set Region
On selection of the Filter Control viz, set the pSelectedRegion parameter passing in the value from the Region for Param field. Set the field to <empty string> when deselected
Create a dashboard filter action
Deselect Marks
On select of the Filter Control viz on the dashboard, target the Filter Control sheet itself, passing in the specific fields of True = False.
And this should complete the required elements. My published viz is here.
This week’s #WOW2024 challenge was run live at the #Datafam Europe event in London and was a combo with the #PreppinData crew. If you want to have a go at shaping the data required for this challenge yourself, then check out the PreppinData challenge here. Otherwise, you can use the data provided in the excel workbook from the link in the #WOW2024 challenge (I’m building based on this).
Modelling the data
There are 3 data sources for this challenge which we need to relate together. We have
Attraction Locations – a list of attractions in London with their lat and long coordinates
Tube Locations – a list of tube stations in London with their lat & long coordinates
Attraction Footfall – a list of attractions with their annual footfall
Connect to the Excel file and add Attraction Locations to the canvas. Then add Tube Locations and then create a relationship calculation of 1=1 to essentially map every attraction to every tube station.
Then add Attraction Footfall to the canvas and relate it to Attraction Locations by setting Attraction Name = Attraction
Finally, in the viz we have to understand the distance between a selected attraction (the start point) and other attractions (the end point), so we need to have an additional instance of Attraction Locations to be able to generate the information we will need between the start and end. So add another instance of Attraction Locations and set the relationship as Attraction Name <> Attraction Name
To make things a bit easier for reference purposes, rename Attraction Locations to Selected Attraction and Attraction Locations1 to Other Attractions (just right click on the data connection in the canvas to do this).
Building the Footfall Bar Chart
On a new sheet add Attraction Name (from Selected Attraction) to Rows and add 5 Year Avg Footfall to Columns. Change this from SUM to AVG (as the data consists of multiple rows per year and this value is the same for each row associated to an attraction). Sort the chart descending.
Click on the 2 nulls indicator and select to filter the data which will remove the bottom two rows and automatically add 5 Year Avg Footfall to the Filter shelf.
Manually increase the width of each row. Set the format of the 5 Year Avg Footfall to be in millions (M) to 2dp, and then show mark labels and align middle left.
Create a parameter to capture the selected attraction
pSelectedAttraction
string parameter defaulted to St Paul’s Cathedral
show the parameter on the screen.
We need to identify which attraction has been selected, so create
Is Selected Attraction
[Attraction Name]=[pSelectedAttraction]
and then add this to the Colour shelf. Adjust the colours accordingly and set an orange border. Then add Attraction Rank to Rows. Set it to be a discrete dimension (blue pill) and move it to be in front of Attraction Name.
Set the font of the row labels to be navy, hide the row label names (hide field labels for rows), hide the axis (uncheck show header), don’t show tooltips, and remove all row/column dividers, gridlines and zero/axis lines. Set the background of the worksheet to be None (ie transparent). Update the title of the sheet and then name the sheet Footfall or similar.
Building the map
We’re going to use map layers for this, and will build 4 layers
the selected attraction
the other attractions
the tube stations
the buffer circle
When using map layers we want to work with spatial data, so we’ll start by creating a point for the selected attraction
Double click on this and it will automatically generate a map. Add Is Selected Attraction to the Filter shelf and set to True so only 1 mark should display, Add Attraction Name to Detail. Show the pSelectedAttraction parameter. Change the mark type to shape and select a filled star. Set the Colour of the shape to navy and add an orange halo. Update the Tooltip.
For the buffer, we need another parameter
pDistance(miles)
float parameter defaulted to 1 that ranges from 0.5 to 2 with a step size of 0.5
And drag this onto the canvas and drop when the Add Marks Layer option appears
This will create a new marks layer, which we can rename to Buffer. Reduce the opacity of the colour to 0%. Move the marks layer so it is at the bottom (below the other marks card) , and set the disable selection option so when you move the cursor over the map the buffer circle does not highlight.
Adjust the background layers of the map so only the Postcode Boundaries are visible.
To add the tube stations, we first need to create
Tube Station Point
MAKEPOINT([Station Latitude],[Station Longitude])
Then drag this onto the canvas to create a new marks layer. Add Station to the Detail shelf of this new marks card, and move the marks card so it is below the Selected Attraction marks card.
We don’t want all the stations to display. We just need to show those up to 1.5x the buffer distance, so we need
Distance to Tube Station
DISTANCE([Selected Attraction Point], [Tube Station Point], ‘mi’)
format to a number with 2 dp and then create
Tube Station Within Range
[Distance to Tube Station]<= 1.5 * [pDistance(miles)]
Add this to the Filter shelf and set to True.
We want the size of the displayed stations to differ depending on whether they’re inside the buffer or not, so create
Tube Station Within Buffer
[Distance to Tube Station] <= [pDistance(miles)]
and add this to Size. Change the mark type to circle, then adjust the size as required. Change the colour to orange and add a white border. Add Distance to Tube Station to Tooltip and update. You may want to adjust the size of the shape on the Selected Attraction marks card too, so it’s bigger than the tube stations.
The stations need to be labelled based on the closest x number of stations that are within the buffer. For this we need a parameter
pTop
integer parameter defaulted to 5 that ranges from 5 to 20 with a step size of 1.
We need to rank the stations based on the distance, so create
Station Rank
RANK(SUM([Distance to Tube Station]), ‘asc’)
We’re also going to label the stations with a letter based on their rank
Rank Stations as Letters
CHAR([Station Rank] + 64)
but we only want to show labels for the ‘top’ ranked stations, so create
Label Stations
IF MIN([Tube Station Within Buffer]) AND [Station Rank]<=[pTop] THEN [Rank Stations as Letters] END
and add this to the Label shelf. Adjust the table calculation settings, so the calculation is computing by both Station and Tube Station Within Buffer.
Set the labels to be aligned middle centre, and allow labels to overlap other marks. If things are working as expected, then if you increase the buffer distance to 1.5 miles and the pTop parameter to 20, you should see that not all stations within the buffer circle are labelled
To add the other attractions, we need to create
Other Attraction Point
MAKEPOINT([Attraction Latitude (Attraction Locations1)],[Attraction Longitude (Attraction Locations1)])
and drag this onto the canvas to Add a marks layer. Move this layer so it is beneath the Selected Attraction marks card, and add Attraction Name (from the Other Attractions) section to Detail
Once again, we want to limit what attractions display, so need
[Distance to Other Attraction]<= 1.5 * [pDistance(miles)]
and add this to the Filter shelf and set to True.
Add Distance to Other Attraction to the Tooltip shelf and update. Change the mark type to shape. The shape needs to differ whether it’s within the top x closest attractions that’s inside the buffer or not. So we need
Rank Other Attractions
RANK(SUM([Distance to Other Attraction]), ‘asc’)
and then
Top X Attraction in Buffer
IF [Rank Other Attractions] <= [pTop] AND MIN([Other Attraction within Buffer]) THEN MIN([Attraction Name (Attraction Locations1)]) ELSE ‘Not Top X’ END
Add this to the Shape shelf. Set the table calculation so it is computing explicitly by both Attraction Name and Other Attraction Within Buffer. Setting the specific shape for each of the named attractions that could show is fiddly, so I just chose to leave as per the default values listed. The only shape I explicitly set was the Not Top X which I set to a filled circle. I set the colour of the shapes to dark grey and added a halo of the same colour to make the shape more prominent. The shapes also need to differ in size based on whether they are in the buffer or not, so need
Other Attraction Within Buffer
[Distance to Other Attraction] <= [pDistance(miles)]
Add to the Size shelf and then adjust sizes to suit.
Set the background of the worksheet to None, remove all row/column dividers and name the sheet Map or similar. Finally remove all the Map Options (Map > Map Options > uncheck all selections) to prevent to toolbar from displaying on hover. Test the map functionality by changing the various parameters and entering a new starting location.
Note– in subsequent testing I found that for some attractions where there were either no tube stations or other attractions within the range, the map would disappear. If I get time I’m going to try to work on a solution for this, but I’ll leave as is for now (Lorna’s published solution has the same issue).
Building the Tube Station Rank Bar
On a new sheet add Station to Rows and Distance to Tube Station to Columns. Add Is Selected Attraction to Filter and set to True. Sort the chart ascending, so closet is listed first.
We only want to display the stations that are within the buffer, so add Tube Station Within Buffer to Filter and set to True.
We also want to restrict this list to just those that are the closest ‘x’ to the attraction based on the pTop parameter. Add Station to the Filter shelf and on the General tab, select Use all and then select the Top tab and add the condition to display the bottompTop by Distance to Tube Station.
However, this doesn’t quite show the correct results, as the Top n filtering has been applied BEFORE the other filters on the shelf. To resolve this we need to add Is Selected Attraction and Tube Station Within Buffer to context (right click each pill on the filter shelf).
Add Station and Distance to Tube Station to the Label shelf, and adjust the label to display the text as required and align middle left. Change the mark type to bar and manually widen the width of each row so the labels are readable. Adjust the colour of the bars.
For the circle labels, we need a ‘fake’ axis – double click into Columns and manually type MIN(-0.05). Move the pill that is created to be in front of the Distance to Tube Station pill.
Change the mark type of the MIN(-0.05) pill to circle and remove the fields from the Label shelf. Add Rank Stations as Letters to the Label shelf instead and adjust the table calculation so it is explicitly computing by Station. Format the label and align middle centre.
Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.
Don’t show the Tooltip, remove all row/column dividers, hide the axis and the Station column. Hide all gridlines, axis lines, zero lines. Format the background of the workbook to be None (ie transparent).
Update the title of the sheet referencing the parameters as required, and name the sheet Tube Station Rank Bar or similar.
Building the Tube Station Rank Bar
On a new sheet add Attraction Name (from the Other Attractions data set) to Rows and Distance to Other Attraction to Columns. Add Is Selected Attraction to Filter and set to True. Sort the chart ascending, so closet is listed first.
We only want to display the other attractions that are within the buffer, so add Other Attraction Within Buffer to Filter and set to True.
We also want to restrict this list to just those that are the closest ‘x’ to the attraction based on the pTop parameter. Add Attraction Name to the Filter shelf, on the General tab, select Use all and then select the Top tab and add the condition to display the bottompTop by Distance to Other Attraction.
Add Is Selected Attraction and Other Attraction Within Range to context.
Add Attraction Name (from the Other Attractions data set) and Distance to Other Attraction to the Label shelf, and adjust the label to display the text as required and align middle left. Change the mark type to bar and manually widen the width of each row so the labels are readable. Adjust the colour of the bars.
Double click into Columns and manually type MIN(-0.1). Move the pill that is created to be in front of the Distance to Other Attraction pill.
Change the mark type of the MIN(-0.1) pill to shape and remove the fields from the Label shelf. Add Attraction Name to the Shape shelf. Set the colour of the shape. Edit the shape for each Attraction so it matches the shapes assigned to the attractions on the Map sheet. Unfortunately, this is a bit fiddly and just a case of trial and error which involves changing the parameters to try to ensure all the options are presented at least once of each of the charts. There is probably a better way, but I’d have to rebuild something so sorry!
Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.
Don’t show the Tooltip, remove all row/column dividers, hide the axis and the Attraction Name column. Hide all gridlines, axis lines, zero lines. Format the background of the workbook to be None (ie transparent).
Update the title of the sheet referencing the parameters as required, and name the sheet Tube Attraction Rank Bar or similar.
Adding the interactivity
Add the sheets onto the dashboard making use of layout containers to get the objects positioned where required. Format the dashboard to set the background to the light peach colour. How I’ve organised the content is show by the item hierarchy below
Create a parameter dashboard action
Select attraction
On select of the footfall bar chart, set the pSelectedAttraction parameter with the value from the Attraction Name field. Keep the value when the mark is deselected.
And at this point, you should hopefully now have a functioning dashboard. My published version is here.
This week’s #WOW2024 challenge was a guest post by Tomoki Goda. The main focus of the challenge was to be able to switch between light and dark mode, but there’s so much more going on, this blog could take a while!
I also have to admit, I didn’t manage to complete this without help and also looking at the solution workbook. It may be if I’d left it and come back to it another time I’d have figured it out, but time is so precious at the moment, it was more likely if I’d left it, I would have struggled to return to it, and then this blog wouldn’t have got written either. But I’ve learned something, so that’s the win in my book 🙂
Setting up the parameters
There are 3 parameters required for this challenge.
pTheme
This parameter will control the mode to display and I set it as a boolean parameter defaulted to true and aliased as True = Light Theme and False = Dark Theme
pRegion
This parameter will capture the Region associated to the KPI the user has interacted with on the dashboard. This is a string parameter defaulted to <empty string>
pCategory
This parameter will capture the Category associated to the Category Sales bar chart that the user has interacted with on the dashboard. This is a string parameter defaulted to <empty string>.
Building the Region KPI chart
ON a new sheet ad Region to Rows and then double click into the Rows shelf manually type MIN(1.0) to create a fake axis. Increase the Size to the largest possible and set the view to Entire View.
Change the Mark Type to Bar. Add Region to the Label shelf. Format the Sales field to be $K to 1 dp and also add to the Label shelf. Adjust the font size and align middle centre. Edit the MIN(1.0) axis to be fixed from -0.2 to 1.2 to allow some spacing between the colour blocks.
Show the pRegion and pTheme parameters. Add Order Date to the Filter shelf and choose Years , then select all years, Show the Year filter and display as a single value list.
Create a new field
Is Selected Region
[Region] = [pRegion]
Add this to the Colour shelf.
Also create a new field
Show Light Theme
[pTheme]
Add this to the Detail shelf initially, then select the ‘hierarchy’ symbol to the left of the pill and change the symbol to the Colour one – this will add two pills to the colour shelf
Drag the Show Light Theme pill so it is listed above the Is Selected Region pill. Enter the name of a Region into the pRegion parameter (eg East), and then adjust the colours for when pTheme=Light Theme is selected
Now change the pTheme parameter so Dark Theme is selected and adjust the colours again
Hide the Region field and the axis (uncheck show header). Don’t show the Tooltip. Hide all row/column dividers, gridlines, zero lines, axis rulers and axis ticks. Set the border on the colour shelf to None, and most importantly, set the worksheet background colour to None (ie it’s transparent). This will become noticeable later when we add the content to the dashboard.
Finally we will need some additional fields which will help with the interactivity on the dashboard later, when we don’t want the marks that have not been selected to ‘fade’.
Region for Param
IIF( [pRegion]=[Region],””, [Region])
True
TRUE
False
FALSE
Add all 3 fields to the Detail shelf. Name the sheet Region Sales KPI.
Building the Category by Sales bar chart
On a new sheet add Category to Rows. Then go back to the Region Sales KPI sheet and set the Year(Order Date) filter to apply to worksheet > selected worksheets > and select the relevant sheet.
Now, I had a couple of attempts at building this. From what I could tell, the Category label wasn’t a usual ‘row heading’, as we needed to give it a specific coloured background on selection. It also had to be built within the same sheet, as the same technique was applied to the Sales by Sub-Category bar chart which was a scrollable section. I tried using a dual axis of Sales and Regional Sales in conjunction with a ‘fake’ axis for the header, but found the width of the fake axis had to match the width of the dual axis, so my header section was too wide. After a lot of trial and error, I ultimately had to ask my colleague, Sam Parsons, if he could figure it out, which he did in 5 minutes using dual axis and Measure Names.
Add Sales to Columns and sort descending. Show the pRegion, pCategory and pTheme parameters and ensure pRegion has a value (eg East).
Create a new field
Selected Region Sales
IF [Region] = [pRegion] THEN [Sales] END
format this to $k to 1dp, and then drag onto the canvas and drop on the Sales axis when the two ‘green column’ icon appears.
This will automatically add Measure Names and Measure Values into the view. Move Measure Names from Rows to the Colour shelf, and also add another instance of Measure Names to the Size shelf. Add Show Light Theme to the Detail shelf, and then set to be an additional field on the Colour shelf. Move it so it is listed above the Measure Names colour pill. Adjust colours of the bars for the light and dark them modes as before.
Reorder the measures in the Size legend box so Selected Region Sales is listed first and so is smaller. Manually increase the width of each row, and then adjust the sizes from the size legend so the difference between the bar widths is not so great.
Create a new field
Label Splitter
IF [pRegion] <> ” THEN ‘ / ‘ END
and add this, Sales and Selected Region Sales to the Label shelf. Arrange the pills as required, align middle left and ensure Allow labels to overlap other marks is selected
Set the pRegion parameter to <empty string> and verify the label displays as expected, Hide the Null indicator. Update the Tooltip as required.
For the header, we need another measure we can use which is on the same axis as the Measure Values, but is negative, so it sits to the left of the bars we already have.
Header Plot
Window_MAX(SUM([Sales])) * -0.25
This takes the maximum value of the Sales bar that is displayed in the chart and applies a proportion, so we don’t need to attempt to ‘fix’ the axis in anyway. Add this to Columns, set to dual axis and then synchronise axis. Set the mark type on the All marks card to bar. You’ll probably have something like…
On the Header Plot marks card, remove the three fields on the Label self, and add Category to the Label shelf instead.
Enter the name of a Category into the pCategory parameter (eg Technology). Create a new field
Is Selected Cat
[Category] = [pCategory]
and drag this and drop it directly onto the Measure Names pill that is on the Colour shelf of the Header Plot marks card. Adjust the colours as required, changing the pTheme parameter to dark mode too.
Delete the text from the Tooltip of the Header Plot marks card.
Hide the Category field and the axis (uncheck show header). Don’t show the Tooltip. Hide all row/column dividers, gridlines, zero lines, axis rulers and axis ticks. Set the border on the colour shelf to None, and once again, most importantly, set the worksheet background colour to None (ie it’s transparent).
The title of the sheet will also need to change colour when the mode differs, so create
Title Light
IIF([Show Light Theme],”Sales by Category”,””)
Also create
Show Dark Theme
NOT([pTheme])
and then
Title Dark
IIF([Show Dark Theme],”Sales by Category”,””)
Add Title Light and Title Dark to the Detail shelf of the All marks card and then update the title of the sheet so both pills are listed and coloured based on the mode – the text for Title Light should be blackand the text for Title Dark should be white (though at this point you won’t see this show up when you change the mode).
Finally, as before, we will need some additional fields which will help with the interactivity on the dashboard later, when we don’t want the marks that have not been selected to ‘fade’.
Category for Param
IIF( [pCategory]=[Category],””, [Category])
Add this and True and False to the Detail shelf of the All marks card. Label the sheet Sales by Cat or similar.
Building the Sub-Category by Sales bar chart
The simplest way to build this sheet is to start by duplicating the Category by Sale bar chart sheet, and then drag Sub-Category and drop it directly on top of the Category pill on the Rows shelf. Re-sort by Sales descending.
On the Header Plot marks card, also drag Sub-Category and drop it directly onto the Category pill on the Text shelf.
Manually increase the width of each row and then hide the Sub-Category column (uncheck Show Header)
Create new fields
Title Light Sub Cat
IIF([Show Light Theme],”Sales by Sub-Category”,””)
and then
Title Dark Sub Cat
IIF([Show Dark Theme],”Sales by Sub-Category”,””)
and drag these to directly on top of the Title Light and Title Dark pills on the Detail shelf of the All marks card. Update the title of the sheet to reference these new pills.
Name the sheet Sales by Sub Cat or similar.
Building the rounded borders
The rounded borders displayed on the dashboard are based on utilising annotations on a ‘dummy’ sheet, as described in these blog posts (here and here).
I created a new field
Dummy
“”
And added this to the Detail shelf on a new sheet. I set the background of the worksheet to none, the mark type to polygon and the sheet to entire view. I then added an annotation, resized it to be as large as possible, and set the properties so the shading was set to none, the corners to very rounded and a dark thin border was applied.
I named this sheet Rounded Edge Light 1. I then duplicated to create a 2nd one and named it Rounded Edge Light 2. I then duplicated again, but this time changed the shading of the annotation to be dark grey/ brown, and named this sheet Rounded Edge Dark 1
Duplicate this sheet again and name Rounded Edge Dark 2.
We now have all the components needed to build the dashboard.
Building the core dashboard
Start by creating the layout for the 3 core ‘chart’ sheets and the parameter/filter controls. I used a combination of horizontal and vertical layout containers and adjusted padding to get the layout required. The image below shows how I laid out the display in the item hierarchy section. Note that all the background of all the containers and the objects on the dashboard are set to None (ie transparent).
Adding the rounded borders
With the theme set to Light Theme, set the option to be Floating and drag on the Rounded Edge Light 1 sheet and position it over the Sales by Category chart. Adjust the height and width until you’re happy, and remove the sheet title. From the context menu of the object, set the floating order so the border sheet is ‘behind’ the bar chart (send backward). This allows the bars to still be clicked on and interacted with.
Then with the border object still selected, set the visibility to only show when Show Light Theme is true
Repeat the same process with the Rounded Edge Light 2 sheet, floating it over the top of the Sales by Sub-Category bar chart.
Then switch the theme to Dark Theme. The borders should disappear. Now repeat the above process with the two Rounded Edge Dark sheets, but this time when controlling the visibility of each sheet, select the Show Dark Theme field instead.
If you’ve followed the steps, you hopefully should have something that looks like
Setting the overall dark background
The final step to get the completely dark background is to float a blank object onto the dashboard. Resize the blank object to be positioned at 0,0 and sized 1000 x 800 (ie the same size as the dashboard)
Adjust the floating order of this object and this time set it to Send to Back so it is the very bottom ‘layer’. Then set the background colour of the blank object to the relevant dark brown/grey colour, and then finally set the visibility to only display when Show Dark Theme is true.
Test the display by switching the theme in the parameter.
Adding the interactivity
We need multiple dashboard actions to control the behaviour of the dashboard.
Set Region
Parameter dashboard action that on select of the Region Sales KPI sheet only, sets the pRegion parameter with the value from the Region for Param field.
Set Category
Parameter dashboard action that on select of the Sales by Cat sheet only, sets the pCategory parameter with the value from the Category for Param field.
KPI Deselect
Dashboard filter action that on select of the Region Sales KPI sheet on the dashboard targets the Region Sales KPI sheet directly passing the fields that set True = False.
Set Category Deselect
Dashboard filter action that on select of the Sales by Cat sheet on the dashboard targets the Sales by Cat sheet directly passing the fields that set True = False.
Set Sub-Cat Deselect
Dashboard filter action that on select of the Sales by Sub Cat sheet on the dashboard targets the Sales by Sub Cat sheet directly passing the fields that set True = False.
And with all this, hopefully you have a fully interactive workbook. My published viz is here.
It was Kyle’s turn to set the challenge this week. Like him, I don’t have a need to use map / spatial data much, so whenever there’s a WOW challenge involving them it always makes me think a bit harder (and usually refer to some documentation).
Connecting to & modelling the data
I followed the links in the challenge requirements and downloaded the Shapefile option from each page
This downloaded zip files (one did take some time to download). I then extracted the zip files which generated several files.
In Desktop, I then chose to connect to the Spatial file option and when I navigated to the file location where I had unzipped the data, only the .shp file was available for selection.
I connected to the School District Characteristics data source first, then clicked the ‘carrot’ to access the context menu of the data source, and selected open to access the physical layer of the data canvas
I then clicked Add against the connections section to add another spatial file data source, selecting the School Neighbourhood Poverty file this time and changed the join type between the two data source fields to use the intersects option.
Building the bar chart
On a new sheet add Statename to the Filter shelf, and select Washington. Add Lea Name to the Rows. Create a new field
and add this to Columns and sort descending. Widen each row slightly, and increase the width of the Lea Name column a bit. Remove all gridlines, and remove the axis title, and hide the Lea Name column heading. Update the Tooltip as required and update the sheet title.
Building the map
Create a new sheet. Add the Geometry field from the School District Characteristicsset of data to the Detail shelf.
Go back to the bar chart sheet, and update the Satename filter so that it also applies to the sheet you’re building the map on. The map should now be filtered to Washington too. Add Lea Name to the Detail shelf and # Schools to the Tooltip and adjust accordingly.
From the Map > Background Layers menu option, uncheck the options on the Background Map Layers section, so just the Cities and Streets, Highways/Motorways.. options remain selected. Adjust the Colour of the map (via the colour shelf)
Then drag the Geometry field from the School Neighbourhood Poverty data source section onto the canvas and drop it when the Add a Marks Layer section appears
This will add a second marks card. Name this marks card Schools and the other one Districts.
On the Schools marks card, add Name to the Detail shelf and then update the tooltip as required. Remove the row & column dividers.
Adding the interactivity
Add the 2 sheets onto a dashboard side by side and show the Statename filter. Add a dashboard filter action
Filter District
On Select of the bar chart, target the Map passing all fields. Show all values when selection is cleared.
Clicking on a bar should now filter the map and ‘zoom in’ just to that district with the relevant school marks visible.
Hot of the press with the release of 2024.3, Sean set this challenge to focus on the ability to use table extensions. As a result you will need at least v2024.3 of Tableau Desktop or Tableau Desktop Public installed. At the point of writing, this challenge cannot be completed on Tableau Public itself via web authoring though.
Build the scatter plot
Format Sales and Profit to be $ with 2 dp. Add Sales to Columns and Profit to Rows and add Customer Name to Detail. Change the mark type to circle, reduce the opacity to around 30%, add a blue border and increase the size of the marks. Format the zero lines to be more prominent. Add Segment to the Filters , select all options, and set to applyto worksheets > all using this data source
Build the table extension
On a new sheet, choose Add Extension from the marks type drop down, and on the add an Extension dialog, select the built by Tableau + Salesforce option and then select the Tableau Table option
Select Open on the next screen, and then select OK to the next dialog box.
Add Customer Name, Order Date (as a continuous exact date – green pill), Sales and Profit to the Detail shelf.
Move your mouse to be in front of the SUM(Sales) heading text, and then click on the sort icon that appears a couple of times to get the data sorted by Sales descending. Double click on the SUM(Sales) heading label and edit the label to just Sales. Repeat with the SUM(Profit) heading label.
Click on the context menu associated to the Sales column and select Format
Set the Formatting Type to be Data Bars and change the Fill colour to green
Format the Profit column to have a Formatting Type of Colour Scale and select a diverging colour palette
Click the Format Extension button on the Marks card shelf or the Table Settings icon on the formatting toolbar to load the Format Extension dialog
Change the options so Show Toolbar is Off, Show Column Filters is On and Show Excel Download is On
Adding the interactivity
Add the 2 objects into a horizontal container in a dashboard. Float the Segment filter control and verify changing the value affects both the scatter and the table.
Then add a dashboard filter action
Filter Table
On select of the Scatter, target the Table passing all fields. Show all values when selection cleared.
And that should be it. Unfortunately as Tableau Public doesn’t yet support the extension, I don’t have my published version to share.
Note – I did have some issues getting the table to ‘fit’ completely into the dashboard. I found if I used my larger second screen, ensured the application was maximised, then it would fit properly. Using the application on my laptop screen, it was sometimes a bit hit and miss. This has been raised to the development team.
For this week’s challenge, Kyle looked to solve a problem that he’s seen discussed within another blog – how to solve a highlighting problem when filtering donut charts.
I’ve been away on a little holiday abroad for a family wedding, so am on catch up this week. So I’m going to make this as brief as I can as time is limited.
Building the donut charts
Use the steps described in this blog post I wrote for my company to build a donut chart using the dual axis method.
For the Category donut chart, you will need Category on Colour and Sales on Angle of the outer Pie Chart. For the inner circle, you will need to add Sales to Text. Adjust the text as required. Sales needs to be formatted to $ with 0 dp.
For the Sub-Category donut chart, you will need to add Category to Colour. Then add Sub-Category to Detail and click on the 3 dots to the left of the Sub-Category pill and change to also add to Colour.
To adjust the colours, edit the colour legend, select all the options within the same Category. Select a sequential colour palette that matches the core colour for the category, then select Assign Palette. The colours should change to a range of that colour.
Create a new field
# Products
COUNTD([Product ID])
and add this to the Angle shelf. Add Sales to the Tooltip shelf and adjust the tooltip.
For the inner circle, add #Products to Text. Adjust the text as required
Filtering the donut
Add the two sheets to a dashboard. Add a dashboard filter action
Filter Cat
On select of the Category donut, target the Sub-Category donut chart passing in all fields. Keep filtered values when selection cleared.
Stopping the Category donut from being highlighted
Create new fields
True
TRUE
False
FALSE
and add these to the Detail shelf on the All Marks card of the Category donut sheet.
Then create a dashboard filter action
Unhighlight
On select of the Category donut on the dashboard, target the Category donut sheet itself, passing in the fields Tue = False. Show all values when selection cleared.
Now when the Category donut is clicked on, the other segments won’t fade. However, the selection is still visible – the edges of the pie are displayed.
Stop showing the selected section of the pie
For this we employ a trick mentioned in the blog post referenced in the challenge. Create a new field
Dummy
‘Dummy’
and add this the Detail shelf of a new sheet. Change the mark type to polygon so nothing is visible.
Add this to the dashboard as a floating object – make it small and place somewhere inconspicuous
Whilst the selections will still be visible when testing on Desktop, once published to Tableau Public, the presence of the polygon forces the whole dashboard to be rendered server side rather than client side. This reduces the amount of interactivity, and consequently the pie chart segments don’t display when clicked.
Donna + DataViz 