KNOWLEDGE BASE

Creating a Calculated Field Using Dimensions with a Cube Data Source


Published: 17 May 2013
Last Modified Date: 06 Mar 2020

Question

How to create a calculated field using dimensions from a multi-dimensional (cube) data source.

Environment

  • Tableau Desktop
  • Multi-dimensional (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.
  1. Drag [Order Date] to the Columns shelf
  2. Right-click YEAR(Order Date) on the Columns shelf and select Sort…
  3. In the Sort dialog, do the following and close the dialog:
    1. For Sort By select Manual
    2. Move 2016 to the bottom
  4. Right-click the 2017 header in the view and select Exclude
  5. Select Analysis > Create Calculated Field
  6. 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()
  7. Drag [Index] to Text on the Marks card
  8. 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

     
  9. 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

     
  10. 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

     
  11. 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

     
  12. Drag [Headers] to the Columns shelf
  13. Right-click YEAR(Order Date) on the Columns shelf and uncheck Show Header
  14. Right-click Order Date / Headers in the view and select Hide Field Labels for Columns
  15. Drag  [2018 Sales (cube)], [2019 Sales (cube)], and [% Difference (cube)] to Text on the Marks card
  16. Click Text on the Marks card and click the button
  17. 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.

Additional Information

Cube data sources pre-aggregate measure values for the specified dimensions before the data is imported into Tableau Desktop. Therefore using dimensions in a calculation can potentially create inaccurate results.

To voice your support for the inclusion of this enhancement in a future product release, add your vote to the following Community Ideas:

Discuss this article... Feedback Forum
Did this article resolve the issue?