**Published:**03 Jul 2013

**Last Modified Date:**06 Mar 2017

### Environment

- Tableau Desktop
- Table calculations

### Resolution

The following instructions can be reviewed in the attached workbook.### Step 1: Create a non-additive Calculation

- Select
**Analysis**>**Create Calculated Field** - In the Create Calculated Field dialog box, do the following and click
**OK**: - Name the field. In the example, this field is called "Non-Additive Calc" and enter the following formula:
SUM([Sales]) / SUM([Profit])

### Step 2: Create a Table Calculation that relies on a non-additive field

- Select
**Analysis**>**Create Calculated Field** - In the Create Calculated Field dialog box, do the following and click
**OK**: - Name the field. In the example, this field is called "Table Calculation" and enter the following formula:
ZN([Non-Additive Calc]) - LOOKUP(ZN([Non-Additive Calc]), -1)

### Step 3: Build the view

- Drag "Category" to the
**Columns** - Drag Measure names to
**Filter**and Keep the newly created calculated fields. - CTRL + Drag Measure Names to
**Rows** - Drag Order Date to
**Rows**as well. - Drag Measure Names to the view
- Right-click on
**Table Calculation**and select**Compute using**>**Table (down)** - (Optional) Right-click Measure Values and select
**Format**>**Numbers**section >**Number (Custom)**> Set Decimal to**1**.

**NOTE:**In the view, it is expected that the Table calculation shows 27.8 + (-)1.3 + (-)3.3 =

**23.2**for the year 2013. However it shows -2.14 which corresponds to: <this year's grand total> - <last year's grand total>

### Step 4: (Optional) Duplicate the sheet

Right-click the sheet and select**Duplicate sheet**to compare the results.

### Step 5: Fix the Grand Total for Table Calculation

Duplicate the Measure on which the data relies on, in this example: Category.- In the Dimension Data Pane, right-click the "Category" dimension and select
**Duplicate**. - Add this duplicated dimension named "Category (copy)" to
**Details**.**NOTE:**This should cause all the values in the table to also appear in the Grand Total box, so that multiple numbers appear in one Grand Total cell in the view. - Create a calculated field:
- Select
**Analysis**>**Create Calculated Field** - In the Create Calculated Field dialog box, do the following and click
**OK**: - Name the field. In the example, this field is called "Non-Additive Calc" and enter the following formula:
IF FIRST() = 0 THEN WINDOW_SUM([Table Calculation]) END

- Select
- Replace the original measure in the view, example [Table Calculation], with [Table Calculation GT Fix].
- Right click on [Table Calculation GT Fix] in the view and choose
**Edit Table Calculation** - Calculated Field selection is set to [Table Calculation Grand Total Fix], choose to set
**Compute Using: Category (copy)**.

**NOTE:**If the number is still not right, double check that when the Calculated Field drop down has [Table Calculation] selected, the Compute Using is set as needed (usually Table Across or Table Down, but

*never*the dimension copy created earlier). More information can be found below in the

**Additional Information**section of this article.

### Cause

Grand Total set to "Automatic" computes the measure across the entire data set (as though the table in the view does not exist).If the measure in the view has an aggregation applied, such as SUM(measure) or MIN(measure), then Grand Total automatically applies the measure's aggregation to the measure across the entire data set. Starting in Tableau Desktop 8.1 the Grand Total aggregation can be changed by switching "Automatic" to "Sum", "Average", etc., per Grand Totals and Aggregations.

When the measure in the view is a Table Calculation, then the field will appear as AGG(table calculation) and the aggregation type of the Grand Total cannot be changed. Also, if the Table Calculation refers to any fields that use a nonadditive or semiadditive (examples: divide, multiply, MIN(), AVG(), etc.), then the Grand Total will not equal the sum of the numbers in the view. In other words, if using a Table Calculation with fields that use SUM() and + or - only, the Grand Total may appear correct due to the additive functions computing across the entire data set in a similar manner to adding the numbers in the view. If using a Table Calculation with fields that use MIN() or multiply (*), then the Grand Totals will probably appear incorrect.

### Additional Information

**Note:**When editing the table calculation of [Table Calculation GT Fix] to show the grand total, you can confirm the result by following the steps below if the results are inaccurrate:

- Right click on [Table Calculation GT Fix] in the view and choose
**Edit Table Calculation** - In the Table Calculation dialog, do the following and click OK:
- In the Calculated Field dropdown, select Table Calculation
- In the Compute using dropdown, select Table (Down).
**NOTE:**By default Compute using for [Table Calculation] nested inside of [Table Calculation GT Fix] will be set to the same setting as [Table Calculation] in the view. - In the Calculated Field dropdown, select Table Calculation GT Fix
- In the Compute using dropdown, select Category (copy)

If your view is structured differently then you may need to use advanced table calculation settings. For more information see Table Calculations: Addressing and Partitioning."

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