KNOWLEDGE BASE

Percent of Total Adds Up to More Than 100%


Published: 01 Oct 2013
Last Modified Date: 10 Aug 2018

Issue

When you use a Percent of Total quick table calculation on a field with a non-additive aggregation, such as Count (Distinct) or Median, the sum of all percentages is greater than 100%. For example:

User-added image

Environment

Tableau Desktop

Resolution

Option 1:
  1. In the view, select the Percent of Total field and drag it to Measures. 
  2. Name the field. 
  3. Right-click the field under Measures and select Edit
  4. Replace TOTAL with WINDOW_SUM
  5. If the numbers are still incorrect, right-click the field in the view and select Compute Using > Table(down) or Table(across). 
Option 2:

Create a Level of Detail based calculation and replace the measure in the view. The calculation for this example would look like the following:
 SUM({INCLUDE: COUNTD([Customer])})
The Count Distinct is calculated at the Region level and then the values are summed in the rows as well as the totals. For more information about Level of Detail functions, see Level of Detail.

The following is the corrected percentage distribution:
User-added image

 

Cause

Non-additive aggregate functions, such as COUNTD(), MEDIAN(), and RAWSQLAGG(), do not calculate correct totals when used with the TOTAL() table calculation function. 
Did this article resolve the issue?