Filter Actions using Grand Totals Do Not Filter Target Worksheets
Published: 08 Oct 2013
Last Modified Date: 21 Jul 2020
IssueAction 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.
ResolutionOption 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.
- Click OK.
- 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.
Option 2: Use Set Action on the Dashboard (See attached Dashboard Actions not working - Solution.twbx)
- 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.
- Click OK.
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
CauseDashboard action filters do not currently apply to grand totals.
Additional InformationTo 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
Discuss this article...