Last Modified Date: 16 Aug 2016
One of the aggregate functions you can use in Tableau is Count (Distinct), which returns the number of unique values in a field. When using Count (Distinct) with subtotals or grand totals, you may not always get the result you want. This article shows how to make the subtotals function as a SUM rather than as Count(Distinct).
For example, suppose you have a simple set of data, such as the Microsoft Excel table below.
Your goal is to determine the number of unique kinds of products sold in each region and then find the total number of unique kinds of products sold across all regions. To achieve this goal, first connect to your Microsoft Excel data source.
In Tableau, your initial view may look like the image below.
You know that the total number of unique items is three: apples, oranges, and grapes. In the West, two unique items were sold. In the East, two unique items were sold. However, you want to total the number of unique kinds per region. Apples and oranges were sold in the West, and apples and grapes were sold in the East, so the number you want is four.
Get the total number of items sold
Create a calculated field that changes the number of unique items at the total, but not for each region.
Select Analysis > Create Calculated Field.
In the calculation editor, do the following:
Name the calculated field Regions and Fruits.
Define the field using this formula:
The new calculation captures every unique combination of Regions and Fruits. For each region, the Count(Distinct) is still the same, but for the total, the Count(Distinct) treats apples in the east as a separate (distinct) item from apples in the west.
Drag Regions and Fruits to Text, replacing Fruit.
Right-click Regions and Fruits, and choose Measure > Count (Distinct).
The view now shows the total of the distinct count of unique items sold in the two regions.
Alternate Search Terms:count d, coundd, count distinct, count