Set actions are a great way to add interactivity into your dashboard. I recently learnt how you could use a set action to show the difference from the average and thought this would be useful in a lot of visualisations.
The example below is using Superstore (US) data.
Step 1 – Create a state versus sales chart
Drag the sales measure to the columns and the state dimension to the rows. Sort this descending to tidy up the chart.
Step 2 – Add an average line
Add an average line to the view by selecting Analytics from the left hand panel and drag and drop an average line onto the table.
Step 3 – Create a Set
Currently this line shows the average sales across all of the states. However we want this line to update as we make a selection of different States.
Create a set using the state dimension. Rick click State in your dimension, select Create Set. A configuration window pops up and I have set this up like the image below.
It doesn’t matter what states are selected at the moment, this just indicates what is currently ‘in’ the set and what the average line will be calculated from. This will update to whatever states a user has chosen when we set up an action.
Step 4 – Create a calculated field to tie the set to the view
Click the drop down arrow next to the Dimension header in your Data window, select create calculated field.
Name your calculated field and type the following –
IF [State Selected Set] THEN [Sales] END.
Drag this new calculation onto columns and a second identical chart will appear on the right. This calculation is saying that when a state(s) is selected then return the sales.
Like before, from the analytics pane, drag and drop and average line onto the Sum(Sales for Selection). An identical average line should appear on the chart on the right.
Step 5 – Create another calculated field, to find the average across selection of states.
Window_Avg (SUM([Sales for Selection]))
Drag this calculation onto Columns. Remove ‘Sales for Selection’ from columns. This is just showing the average for all States as bars.
Step 6 – Create another calculated field called to show the difference in average sales for each state.
SUM([Sales]) – Average across selection
Drag this calculation to columns and to colour on the marks shelf. Remove the second calculation ‘Average Across Selection’ from the view.
A difference in average chart should now be showing on the right. Any state that has sales above the average is coloured with a shade of blue and any state with below average sales is shaded with orange.
Drag Sales to text on the mark shelf.
Step 7 – Set up the set action
We want the average line and different from average to update depending on what states are selected.
Set up set action by going to ‘Worksheet’ on the top ribbon, ‘Actions’ > Add Action > Change Set Values.
A configuration window will appear. Select the current sheet you are working on as your source sheet. Select your State Set as your target Set. Press ok.
Now when you select a state or multiple states, the reference line and chart on the right should update to show the new average Sales and the difference from the average sales per state.
That’s it, you now have a set action showing the difference in average!