Filter Actions using Grand Totals Do Not Filter Target Worksheets
Published: 08 Oct 2013
Last Modified Date: 19 Nov 2017
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.
ResolutionCreate 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.
- 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.
CauseDashboard action filters do not currently apply to grand totals.