KNOWLEDGE BASE

Calculated Field Returning Incorrect Results Sometimes Percentage Exceeds 100%


Published: 28 Mar 2013
Last Modified Date: 02 Nov 2020

Issue

A calculated field is returning incorrect results. For example, a field with the formula:

[Profit]/[Sales]

is not returning the expected ratio, percentage total exceeds 100%, etc.

Environment

  • Tableau Desktop

Resolution

  1. Aggregate the measures within the calculated field. For example, change the calculated field's formula:
    [Profit]/[Sales] to  SUM[Profit]/SUM[Sales]
  2. Remove the calculated field from the view and then re-add it to the view.

Cause

The calculation used the disaggregated (row) values instead of the aggregated (summed) values of the fields.
Below is an explanation of how each option works using the sample data set shown as a reference.

Sample Data Set
Row IDProfitSales
1100300
250600
3710
In non-aggregated formula, such as [Profit] / [Sales], the value of profit divided by the value of sales in each row, then the results are sum up. So the result of the calculated field becomes 100/300 + 50/600 + 7/10 = 0.3333 + 0.0833 + 0.7 = 1.1163.

In aggregated formula, such as SUM ([Profit]) / SUM ([Sales]), all rows of profit and all rows of sales are sum up first, then the result of profit is divided by that of sales. So the result of the calculated field becomes (100 + 50 + 7)  / (300 + 600 + 10) = 157 + 910 = 0.1725.
Did this article resolve the issue?