There can be many use cases for applying dashboard filter on a Worksheet, but this blog illustrates how we can achieve the effect on the below picture.
The dashboard in the picture above shows the overall sales by each subcategory. Upon clicking on any of the bars, only the selected subcategory remains and monthly sales line chart for the selected subcategory appears. Again, clicking on the subcategory will bring back the all the members in the dimension.
When working on one of our client projects, Craig gave us this tip.
Follow the steps below to build the same, using the Sample superstore data:
1. Create Sheet 1: Subcategory sales
On a new sheet, drop Sub-Category on to Rows, Drop Sales (Sum) on to Columns and Labels and format accordingly.
2. Create Sheet 2: Monthly sales
On another sheet, drop Sales (Sum) on to Rows, Drop Order date (Continuous – Monthly) on columns. Also drop Sub-Category on to Detail. This will create a mess of lines, but you are on the right track.
3. Create containers and link both the sheets on a dashboard
– On a new dashboard, first drop a vertical container.
– Drop Subcategory sales sheet into the container.
– In the same container, drop the Monthly sales just below the Subcategory sales sheet.
– Hide the title on both the sheets.
– Go to Dashboard > Actions… (or Control + Shift + D) > Add Action > Filter >
– I am going to name the filter ‘Sub Category to Sales’. On Source Sheets, only select ‘Subcategory sales’. On Target Sheets, only select ‘Monthly sales’.
– Run action on ‘Select’
– Clearing the selection will ‘Exclude all values’
– Target Filters, All fields. Click Ok.
Upon selecting of any of the bars and then unselecting it, hides the line chart from the view (If this is not happening, it is likely that both the sheets are not in the same vertical container.)
This looks great, but we want to take a step further to expand the line chart and minimise the Subcategory to the current selection.
4. Add filter on same Worksheet
– Go to Dashboard > Actions… (or Control + Shift + D) > Add Action > Filter >
– Naming the filter ‘Subcategory filter’. On Source Sheets, only select ‘Subcategory sales’.
– On Target Sheets, from the drop-down select the ‘Subcategory sales’ sheet.
– Run action on ‘Select’
– Clearing the selection will ‘Show all values’
– Target Filters, All fields. Click Ok.
Applying this filter will give us the desired result we were after.
