Filter Actions using Grand Totals Do Not Filter Target Worksheets
Published: 08 Oct 2013 Last Modified Date: 21 Jul 2020
Action filters do not filter the target worksheet(s) from Grand Totals. In other words, when a Grand Total value is clicked (or hovered over) on a view in which a dashboard action filter is set up, the target worksheets are not filtered according to the total value.
In the case of a menu filter, the hyperlink will still appear when hovering over a Grand Total, but following the link will not filter the view as expected.
Option 1: Create a separate Totals worksheet and combine worksheets on a dashboard.
Step 1: Create the Worksheet.
Right-click on the axis displaying Grand Totals and select Add Reference Line, Band, or Box
In the Add Reference Line, Band, or Box dialog, select the Per Pane option.
Select the measure that is generating totals in the first Value drop-down menu.
Select the desired total aggregation (for example, SUM) in the second Value drop-down menu.
Select None in the Label drop-down menu.
Select None in the Line drop-down menu.
Right-click on the axis displaying Grand Totals and deselect Show Header.
Deselect Analysis > Totals > <Row or Column> Grand Totals.
Right-click on the worksheet that displays grand totals and select Duplicate Sheet.
Remove any dimensions that are partitioning the measure that is generating totals.
Right-click on the measure generating totals and select Show Header.
Step 2: Create the Dashboard.
Select Dashboard > New Dashboard.
Drag the original worksheet and the duplicated worksheet to the new dashboard and arrange as desired.
Select Dashboard > Actions.
In the Actions dialog box, select Add Action > Filter.
Select both the original worksheet and the new duplicated worksheet in the Source Sheets list.
Select the desired target sheet in the Target Sheets list.
Option 2: Use Set Action on the Dashboard (See attached Dashboard Actions not working - Solution.twbx)
Step 1: Create Sets 'Year Set' and 'Category Set' on the sheet 1 'Category and Sales by Year' worksheet Step 2: Add these 2 sets to the Filter on the sheet 2 Step 3: Add 2 set actions on the dashboard
Dashboard action filters do not currently apply to grand totals.
To voice your support for the inclusion of this enhancement in a future product release, add your vote to the following Community Idea: Action Filter on Grand Totals