KNOWLEDGE BASE

Filtering a Table Calculation by another Table Calculation


Published: 22 May 2014
Last Modified Date: 25 May 2018

Question

How to filter a table calculation by another table calculation. For example, if the original table calculation is a percent of total sales per customer and the view should be filtered to only the top 10 customers, which is determined by rank, then the percent of total will still be calculated over all customers.

Environment

Tableau Desktop

Answer

Add the table calculation filter to the syntax of the other table calculation using an IF statement.

The attached example workbook uses the sample data set Superstore to demonstrate the following directions:

Step 1: Set up the view

  1. Drag [Customer Name] to the Rows shelf
  2. Drag [Sales] to Text on the Marks card
  3. Right-click [Sales] on the Marks card and select Quick Table Calculation > Percent of Total
  4. Right-click [Sales] on the Marks card and select Compute Using > Table (down)
  5. Select Analysis > Create Calculated Field
  6. In the Calculated Field dialog box that opens, do the following, and then click OK:
    • Name the calculated field. In this example, the calculated field is named "Rank of Sales"
    • In the formula field, create a calculation similar to the following:
      RANK(SUM([Sales]))
  7. Drag [Rank of Sales] to the Filters shelf
  8. In the Filter dialog, select 1 to 10 for the range and click OK.


Step 2: Create the new calculation

  1. Create a new calculation and drag [Sales] from the Marks card into the calculation editor. Tableau Desktop will automatically write out the formula for percent of total sales, which will look like:

    SUM([Sales]) / TOTAL(SUM([Sales]))
     
  2. Wrap each instance of SUM([Sales]) with an IF statement that will filter to ranks lower than 10. The final calculation may look like:

    IF [Rank of Sales] <= 10
    THEN SUM([Sales])
    END
    /
    WINDOW_SUM(IF [Rank of Sales] <= 10
    THEN SUM([Sales])
    END)



    Note: It is also necessary to replace the table calculation TOTAL() with WINDOW_SUM(), as TOTAL will only work with aggregations and not with more complex expressions.
     
  3. Replace [Sales] with the new calculation

Additional Information

Any table calculations on the Filters shelf will not affect any table calculations in the view. Therefore, the filter must be explicitly added to the final table calculation.
Did this article resolve the issue?