Recently, I was building a Tableau dashboard to analyse customer data. In this, I faced a common requirement that instead of counting the total number of records, I needed to count the unique values in a field. For example, I wanted to see the number of unique customers who made purchases, not just the total number of transactions.
In Tableau, using just the COUNT(), we will get the overall count of the values, including the repeated values. As a solution for this, we can use the COUNTD() function. Using this function, we can calculate the distinct count of field values.
In this Tableau tutorial, I will explain how to count distinct values in Tableau using the various methods.
Count Distinct Field Values in Tableau
In Tableau, there are two ways through which we can display the distinct count of the field values, which are as follows.
- Count distinct field values in Tableau with a measure
- Count distinct field values in Tableau with the COUNTD() function
In the examples below, we will see both of these methods to calculate the distinct count of the field values in Tableau.
Method-1: Count Distinct Field Values in Tableau With a Measure
In this method, we will use the count measure feature in Tableau. For this, we will take an example in which we will show the distinct count of the customers who made sales.
If we have used the COUNT() function, it will show the count of every transaction of sales instead of the unique count.
Now, we will create a view by adding the SUM(Sales) to the Columns and the Region to the Rows.
To show the distinct count of the customer sales, add Customer Name to the Text card in the Marks section. After this, click on the Customer Name dropdown and select Measure -> Count(Distinct).
Now, we can see the distinct count of customers who made sales in each region.
This way, we can show a distinct count of the field values in Tableau by using the distinct count measure.
Method-2: Count Distinct Field Values in Tableau With the COUNTD() Function
In this method, we will create a calculated field using the COUNTD() function to show the distinct count of the field values.
To create a calculated field, click on the Analysis tab and select Create Calculated Field.
Enter the formula below in the calculated field using the COUNTD() function.