In order to filter the view, without filtering the total, we can take advantage of table calculations in Tableau, in this case, LOOKUP(). Filters based on table calculations do not filter out underlying data. Instead, the data is hidden from the view, allowing dimension members to be hidden from the view without impacting the data in the view.
Rather than using the Agency Name as the filter, we can use a table calculation, which will "look up" the agency name at position 0 (the position relative to the current row), and return the results. The final product looks and acts the same as the original, however the grand total is reflective of all values, not only those agencies in the view.
I have re-attached the sample workbook using this calculation.
For more information on this solution, check out the Knowledge Base article below.
Thanks Sara. Good to know for future.
I have a question. In the !Filter I have empty value, when I add Month value.. Could I exclude it from the filter?
Please find the attached file (Sheet 5)
1 of 1 people found this helpful
It is possible to exclude the "Date" dimension in the filter by changing the Addressing and Partitioning of the !Filter calculation.
1. Right click on the !Filters field on the filters shelf
2. Select "Edit Table Calculation"
3. Select Compute Using > Advanced
4. Move the "Agency Name" and "Date_A" fields into the "Addressing" pane
5. Click "OK"
For more information on Addressing and Partitioning, and table calculation functions, I would recommend the article in Tableau's Online Help.
Table Calculations (17 minutes)
Addressing and Partitioning
Understanding Table Calculations