Last Modified Date: 15 Aug 2023
Environment
Tableau DesktopAnswer
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
- Drag [Customer Name] to the Rows shelf
- Drag [Sales] to Text on the Marks card
- Right-click [Sales] on the Marks card and select Quick Table Calculation > Percent of Total
- Right-click [Sales] on the Marks card and select Compute Using > Table (down)
- Select Analysis > Create Calculated Field
- 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]))
- Drag [Rank of Sales] to the Filters shelf
- In the Filter dialog, select 1 to 10 for the range and click OK.
Step 2: Create the new calculation
- 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]))
- 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.
- 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.Transform Values with Table Calculations
Computing a Table Calculation by a Dimension Not Present in the View
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials