KNOWLEDGE BASE

Filtering Duplicate Values Generated by Table Calculation on Level of Detail


Published: 06 Jan 2014
Last Modified Date: 03 Sep 2015

Question

How to remove duplicate values generated when using Table Calculations with a dimension on Level of Detail.

Environment

Tableau Desktop

Answer

The following instructions can be reviewed in the workbooks attached below.
  1. Open Tableau Desktop and connect to the Sample - Superstore sample data.
  2. Drag Category to the Rows shelf.
  3. Drag Order Date to the Filters shelf.
  4. In the Filter Field dialog box, select Months, and then click Next.
  5. Select the check box next to January, and then click OK.
  6. Drag Order Date to the Detail card.
  7. Right-click Order Date on the Detail shelf and select Exact Date.
  8. Right-click Order Date on the Detail shelf and select Discrete.
  9. Select AnalysisCreate Calculated Field.
  10. In the Calculated Field dialog box, enter a name for the field.
  11. In the formula field, create a calculated field similar to the following:
    IF FIRST() = 0 THEN WINDOW_AVG(SUM([Sales]), 0, IIF(FIRST() = 0, LAST(), 0)) END
  12. Click OK.
  13. Drag the newly created calculated field from Measures to the Label card.
  14. Right-click the calculated field on Text shelf and select Edit Table Calculation.
  15. Under Compute Using, select Advanced.
  16. Move Category from Partitioning to Addressing, and then do the same for Order Date.
  17. Click OK.
  18. From the At the level drop-down menu, select Deepest.
  19. From the Restarting every drop-down menu, select Category, and then click OK.
Did this article resolve the issue?