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.
In this formula, inside the COUNTD(), we have to use the field for which we want to calculate the distinct count.
For example, I want the distinct count of products for the sales. For this, we will use the following formula.
COUNTD([Product Name])After this, click OK to save the calculated field.
Now, drag this newly created calculated field into the Text card on the Marks shelf. Then add Region to Rows and Sales to Columns.
This will display the number of unique products that made sales in each region. We can also place this calculated field on Rows, Columns, or Tooltip, depending on how you want to visualise the distinct count in the dashboard.
This way, we can count distinct values in Tableau using the calculated fields.
Conclusion
In this Tableau tutorial, we learned how to count distinct values using different methods. First, we used the Count (Distinct) measure option to find the unique values . Then, we created a COUNTD() function to make the distinct count reusable across different worksheets.
We also use the above methods with the Level of Detail (LOD) expressions in Tableau to calculate distinct counts at specific levels.
You may also like to read:
- Create Groups in Tableau
- Level of Detail Expressions in Tableau
- Export Tableau Report in PDF
- Sort by Calculated Field in Tableau
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
