KNOWLEDGE BASE

Changing the Total of a Percent-of-total Calculation When Triggering Different Action Filters


Published: 28 Mar 2017
Last Modified Date: 29 Mar 2017

Question

How to change the total used in a percent of total calculation in a target worksheet based on the Action Filter triggered. For example:
  • When you select a circle on a scatter plot that represents one product, then the percent of total should be the sum of sale for that product divided by the sum of sales for the region that product is in.
  • Then when you select an entire region in a different source worksheet, then the percent of total should be the sum of sales for that region divided by the sum of sales for all regions.

Environment

Tableau Desktop 9 and later versions

Answer

The corresponding Tableau packaged workbook, Filter total in percent-of-total .twbx, is attached to this article for you to download and use along with the steps in this article. 

Step 1: Build the "Per Region" worksheet and create the Action Filter

  1. Select Analysis > Create calculated field...
  2. Create a calculated field with a name like "Blank" with a calculation similar to the following:  ""
  3. Drag [Blank] onto Detail on the marks card
  4. After building the Dashboard, select Dashboard > Actions... > Add Action > Filter...
  5. In the Edit Action Filter dialog, do the following and click OK:
    1. Name the filter. For example "Region % of total"
    2. Choose Dashboard 1 and then Per Region as the Source Sheet. 
    3. Choose Select for the field "Run action on:".
    4. For Target Sheets choose % of Total and Sheet 12
    5. Choose Show all values for the field "Clearing the selection will".

Step 2: Create 2 dashboard action filters for the Scatter Plot.

  1. Select Dashboard > Actions... > Add Action > Filter...
  2. In the Edit Action Filter dialog, do the following and click OK:
    1. Name the filter. For example "Scatter plot to % of total (only region)"
    2. Choose Dashboard 1 and then Per Product as the Source Sheet. 
    3. Choose Select for the field "Run action on:".
    4. For Target Sheets choose % of Total and Sheet 12
    5. Choose Show all values for the field "Clearing the selection will".
    6. For Target Filters, ensure Selected Fields is ticked. 
    7. Click Add Filter... > Select Region and click OK.
  3. Select Dashboard > Actions... > Add Action > Filter...
  4. In the Edit Action Filter dialog, do the following and click OK:
    1. Name the filter. For example "Scatter plot to % of total (everything else)"
    2. Choose Dashboard 1 and then Per Product as the Source Sheet. 
    3. Choose Select for the field "Run action on:".
    4. For Target Sheets choose % of Total and Sheet 12
    5. Choose Show all values for the field "Clearing the selection will".
    6. For Target Filters, ensure Selected Fields is ticked. 
    7. Click Add Filter... > Select Product Name and click OK.

Step 3: Add the blank field to the "% of Total" worksheet

  1. Navigate to the "% of Total" worksheet
  2. Add [Blank] onto Detail on the marks card.
    • Tableau Desktop will merge any Action Filter that filters the same dimensions. In this method we want a separate Action Filter for [Region] for each source sheet, which will allow us to add one of these action filters to Context.
  3. Right-click the filter Action (Region) on the Filters shelf and select Add to Context.

Additional Information

This context filter will filter the level of detail (LOD) calculation used in the denominator of the percent of total calculation.
Did this article resolve the issue?