Creating a Calculated Field Using Dimensions with a Cube Data Source

Answer

Option 1: Use Table Functions

While Tableau calculated fields in cube data sources cannot reference dimensions directly, table functions can reference dimension members in the view. For example, INDEX() can be used to reference the column/row of a dimension value in the view. Or WINDOW_SUM(SUM(1)) can be used to count the dimension members in the view.

Note: This method requires that all dimensions indirectly referenced in calculations are included in the view, but the view can be further modified to hide those dimensions. See Computing a Table Calculation by a Dimension Not Present in the View

The following instructions demonstrate how to create a Crosstab view with columns for 2019 sales, 2018 sales, and a column for the percent difference between those two years. To see these instructions in action (and see additional notes about why this method works), download the workbook from the right hand pane of this article.

Drag Order Date to the Columns shelf
Right-click YEAR(Order Date) on the Columns shelf and select Sort…
In the Sort dialog, do the following and close the dialog:
1. For Sort By select Manual
2. Move 2016 to the bottom
Right-click the 2017 header in the view and select Exclude
Select Analysis > Create Calculated Field
In the Calculated Field dialog box that opens, do the following, and then click OK:
1. Name the calculated field. In this example, the calculated field is named "Index"
2. In the formula field, create a calculation similar to the following:
  
  INDEX()
Drag Index to Text on the Marks card
Create a calculated field with a name like "2018 Sales (cube)" with a calculation similar to the following:

IF INDEX() = 1
THEN SUM([Sales])
END
Create a calculated field with a name like "2019 Sales (cube)" with a calculation similar to the following:

IF INDEX() = 2
THEN SUM([Sales])
END
Create a calculated field with a name like "% Difference (cube)" with a calculation similar to the following:

IF INDEX() = 3
THEN
(WINDOW_SUM([2018 Sales (cube)])-WINDOW_SUM([2019 Sales (cube)]))
/
WINDOW_SUM([2018 Sales (cube)])
END
Create a calculated field with a name like "Headers" with a calculation similar to the following:

IF INDEX() = 2
THEN '2019 Sales'

ELSEIF INDEX() = 1
THEN '2018 Sales'

ELSEIF INDEX() = 3
THEN '% Diff'

END
Drag Headers to the Columns shelf
Right-click YEAR(Order Date) on the Columns shelf and uncheck Show Header
Right-click Order Date / Headers in the view and select Hide Field Labels for Columns
Drag 2018 Sales (cube), 2019 Sales (cube), and % Difference (cube) to Text on the Marks card
Click Text on the Marks card and click the … button
In the Edit Label dialog, remove the enter breaks between AGG(2018 Sales (cube)), AGG(2019 Sales (cube)), and AGG(% Difference (cube))

Option 2: Use Calculated Members

As a workaround, create a calculated member instead. For more information, see How to Create a Calculated Member.

All Support