Introduction to Tableau Count Distinct
Count Distinct is the aggregated function present in the tableau. Distinct stands for the unique value of the dimensions or measures. When we create dimension value for the aggregation then the result of the count distinct is get created into measures of the dataset. A calculated field needs to be created for the Count Distinct function. We created a calculated field by clicking the bottom row present near the dimension value set. All the aggregated functions are available with formula in the tableau. We just need to select dimensions as per our choice. we can count Boolean, string, and numbers also. Count (Distinct) returns the unique value in the field.
Syntax of CounD(expression)
CountD([region])
How Does Count Distinct Work in Tableau?
CountD function is used for displaying a distinct or unique value of the dimension. It will count the distinct value of the number of items in a group and will display it. It will ignore NULL values. Tableau creates a temporary new column in the measure section as a result of the dimension because the result of count is number, and measure contains the quantity of the data. It will count the unique instance of the field. Suppose if we have 20,000 records for the 10 different countries then the distinct value of the country will be 10 or 5. It will aggregate distinct value for the record even if a large number of the dataset is present.
Examples of Tableau Count Distinct
Examples of tableau count distinct are given below:
Example #1
1. We have connected to the sample superstore database in tableau and fetched the order table.
2. Go to sheet1 where we will get to see data separated into measures and dimensions. Dimension is qualitative data and dimension is quantities data.
3. The number of records options available in the table which counts no of the total data available to the data source.
4. Create calculated field name CountD and enter formula COUNTD([subcategory]).
5. We can choose any dimension for the COUNTD.
6. Drag sub-category to columns and region to Rows.
7. Drag COUNTD calculated field to the text formatting option.
8. We will get the below image.
9. We can observe in the below image, distinct count for each subcategory of each region is given as one.
1. We can show you the difference between COUNT and COUNTD.
2. We have created COUNT([sub-category]) calculated filed and add it to the Rows.
3. We can see in the below image average count for accessories for the central region is 183 and the distinct count is 1 because there are the multiple numbers of accessories (item) for the central region and it is counted as 1.
Example #2
1. Drag order table to tableau sheet.
2. Create a Calculated Field CountD(dimension).
3. Drag region dimension to the column, CountD measure created to rows and profit measure to Text filtering option.
4. As per the calculated field we created for the city, we can observe the result for central region CountD is 181 but the actual count was 2323 and the profit is 39,706.
5. We can observe the result for the east region is CountD that is a distinct count is 108 and the profit is 91,523.
6. We can observe the distinct count for each region as the unique count for the regions. We had a distinct count for the city as a string.
Example #3
We will countD for the category dimension for the order date of each city.
- Create calculated field CountD(category).
- Drag to columns and order date to rows.
- Drag CountD to text formatting option.
We can observe that for Aberdeen city for the 2016 year we have distinct count as 1. We have calculated a distinct count for category dimension (category is string value).
Example #4
We are going to calculate the distinct value for the world cup matches the winning team for each country with year database.
- We have downloaded the world cup matched database file from Tableau resources website.
- We have created CountD for the HomeTeam (winning team) because we want to count Distinct or unique value for the winning team.
- Drag Country and Year dimension to rows and CountD to Text formatting option.
- We get the Hometeam count for a year with the country.
- We get the result as for Argentina for the years 1950 and 2000 as HomeTeam as 13 and 13 is the distinct count for both the year meanwhile actual count is 38. For England’s country, the distinct count is 12 for 2000 and 1950 year.
Conclusion
We have learned about how to calculate distinct or unique of the field or dimension. The key point to remember is calculated field needs to be created at first for the CountD aggregation function. The result is always present in measure because it is a number. It is always helpful for the user when they want to find the distinct value of the large dataset.
Recommended Articles
This is a guide to Tableau Count Distinct. Here we discuss How does Count Distinct works in Tableau along with different examples in detail. You may also look at the following articles to learn more –
