KNOWLEDGE BASE

Percent of Total Calculation Returns Unexpected Results


Published: 21 Jan 2016
Last Modified Date: 04 Aug 2016

Issue

Calculated field is not working as expected, when calculating the percentage of a total.

Environment

  • Tableau Desktop 9.2
  • Excel

Resolution

To achieve the desired result, aggregate the values before calculating the percentage ratio. For example:
SUM([Units Sold]) / SUM([Units in Market])
Instead of:
[Units Sold] / [Units in Market]

Cause

The original calculation would run for each row in the data set, and if the calculated field is then used in the view, those results would be aggregated to the level of detail in the view. For example, if the data source has date entries for every month, but the date is viewed by quarter, the values of the calculated field will be summed after calculating the ratio. This would not result in the expected value because the calculation would be aggregating the individual ratio values. 

By aggregating the values, the calculation can still be run for each row of the data set, but if the view provides a different level of detail, such as date by quarter or year instead of month, this calculation will run again, first aggregating all the values for field by summing the data fields by quarter or year respectively, and then calculating the average.
Did this article resolve the issue?