KNOWLEDGE BASE

Computing a Table Calculation by a Dimension Not Present in the View


Published: 14 Apr 2017
Last Modified Date: 24 Apr 2017

Question

How to compute a table calculation by a level of detail that is not in the view.

For example, creating a crosstab that shows the total of AVG(Sales) per [Category], [Segment] and [Region]. We then want to remove [Segment] from the view.
 

Environment

Tableau Desktop

Answer

Both options can be reviewed into the attached workbook "remove dimension from view". 

Option 1: Use Level of Detail (LOD) expressions

  1. Create a calculated field with a name similar to "Avg of Sales Including Segment" and enter a formula similar to:
    { INCLUDE [Segment] : AVG( [Sales] ) }
  2. Replace [Sales] in the view with [Avg of Sales Including Segment].

Option 2: Remove the dimension

Add all necessary dimensions to the view, and then clean the view up to look like extra dimensions had not been added.
  1. Drag [Category] and [Segment] to the Rows shelf.
  2. Drag [Region] to the Columns shelf.
  3. Create a calculated field with a name like "Window Sum of Avg of Sales" with a calculation similar to the following:
    WINDOW_SUM( AVG( [Sales] ) )
  4. Drag [Window Sum of Avg of Sales] to Text on the marks card.
  5. Right-click [Window Sum of Avg of Sales] on the marks card and select Compute using > Pane (Down)
  6. Create a calculated field with a name like "First Filter" with a calculation similar to the following:
    FIRST() = 0
  7. Drag [First Filter] to the Filters shelf.
  8. Click OK to close the Filter dialog.
  9. Right-click on [First Filter] on the Filters shelf and select Compute using > Pane (Down).
    • Note: The table calculation setting for the [First Filter] will always be the same as the table calculation setting for the table calculation in the view.
  10. In the Filter dialog, check True and click OK.
  11. Right-click [Segment] on the Rows shelf and uncheck Show Header.
Please note, any additional measure added to this view will need to be wrapped in WINDOW_SUM() in order to show the value aggregated up to the [Category] level of detail.

Additional Information

By design table calculations only act on the data in the view.
Did this article resolve the issue?