What is the Tableau Countd function?
Tableau Countd function is an aggregate function that allows you to count the number of values or items in a data field or evaluation expression. It evaluates a single data field, calculated field, or an expression and provides a single value as output. Tableau Countd provides the distinct number of values in your data field or expression and any null values are excluded from the evaluation. Using the Tableau Countd function, you can identify the unique list of values in your dataset such as unique values of cities, products, or countries for further data analysis. It returns the number of items excluding any null values.
Table of contents
Key Takeaways
- Tableau countd function is one of the aggregate functions that count the distinct values in a group. It excludes any null or blank values along with duplicates.
- Tableau countd function accepts a data field or an expression as an input parameter.
- You can use countd function with other functions or aggregations in Tableau to create data visualization.
- Tableau countd function can’t handle null values on its own, however, you can add conditional logic to handle such scenarios.
- Refer to the Tableau documentation or community blogs regularly to resolve any technical issues or challenges you may encounter.
Syntax
The syntax for the Tableau COUNTD is:
COUNTD(expression)
For example, the COUNTD([customer_id]) function will return the total number of customers stored in the customer_id data field.
How to Use the Countd function in Tableau?
To use the countd function in Tableau, follow the steps outlined below:
Step 1: Connect your dataset to the Tableau interface. The imported dataset can be visible in the Data Source tab.
Step 2: Navigate to a new worksheet. Select Analysis – Create Calculated Field from the context menu.
Step 3: On the calculated field window, specify the logic to calculate the field along with a meaningful name.
Here, we have created 2 calculated fields, i.e., Number of customers and Number of distinct customers, as shown below.
Step 4: Drag and drop the fields, Region and Segment, to the Rows shelf and Number of customers and Number of distinct customers to the Columns shelf.
Tableau will add the chart in the view as shown below.
Step 5: Click on Show Me on the right corner of the screen and select the text tables icon from the available options.
Tableau will change the visual, as shown below. We adjusted the table to display the measure names.
Step 6: Drag the Measure Names to the Color shelf in the Marks pane. Click on the Color – Edit Colors. Specify the choice of colors for each of the calculated fields.
Now your Tableau visualization is ready for view. As you can see while the Count function provides the total number of customers, the Countd function provides only their unique count.
Note: You can use Tableau countd if statements for condition-based counting.
For example, COUNTD (IF[condition] THEN [Data Field] ELSE 0 END)
While the Tableau countd function broadly works with a single data field, you can use Tableau countd multiple fields either by using a concatenated field or a combination of fields.
If you are using Tableau countd blended data, you may not get the desired results. Ensure that you either use Level of Detail calculations or pre-aggregate the data.
Examples
In this section, we will go through different examples demonstrating the usage of the Tableau Countd function.
Example #1
In this example, we will demonstrate how to use the COUNTD function in Tableau to create a data visualization using the Pharma Sales dataset. This dataset contains the sales details of a pharma company for 2 years, i.e., 2015 and 2016 across various regions and time frames.
To create a data visualization using the Tableau COUNTD function, you may follow the instructions highlighted below:
Step 1: Connect with the Pharma Sales dataset to Tableau using the File – Open. You can view the imported dataset in the Data Source tab of Tableau.
Step 2: Right-click on the Data pane and select Create Calculated Field option from the context menu.
Step 3: In the Calculation Field window, specify the logic for the field and a valid meaningful name.
The total Accounts field will provide a view of the total number of accounts.
The Total Unique Accounts field will provide a view of the distinct number of accounts.
Step 4: Drag and drop the Division field to the Rows shelf and newly created measures i.e. Total Accounts and Total Unique Accounts fields to the Columns shelf, as shown below.
Step 5: Click on the Show Me toolbar and choose the text tables option from the list. Tableau will change the visual from horizontal bars to a text table, as shown below.
Step 6: Drag Measure Names from the Data pane to the Color in Marks shelf. As you can see below, Tableau has added different colors to the measures in the view.
Now your Tableau visualization is ready for data analysis and presentation.
Example #2
In this example, we will demonstrate another scenario of using the COUNTD function in Tableau using the Popular Songs Album dataset which contains concise details about music tracks and their performance across various platforms including essential information like track name, artist(s), release date, and presence in popular playlists and charts on platforms like Spotify, Apple Music, Deezer, and Shazam.
Step 1: Connect to the Popular Songs Album dataset in Tableau using the File navigator.
Note that the file has few null values in the Track Name and Artist Name fields as shown below.
Step 2: Navigate to the Analysis and choose Create Calculated Field option from the context menu.
Step 3: In the calculation window, specify the calculation logic and name of the measure. Here we have created 4 different measures, i.e., Total Artists, Total Distinct Artists, Total Tracks, and Total Distinct Tracks as mentioned below.
Total Artists stores the total number of artists based on the artist’s name.
Total Distinct Artists stores the total number of distinct artists based on the Artist’s name.
Total Tracks stores the total number of tracks based on the track name.
Total Distinct Tracks stores the total number of distinct tracks based on the track name.
Step 4: Drag the newly created measures from the Data pane to the Columns shelf and the Key field to the Rows shelf. Tableau will create horizontal bars for each of the measures.
Step 5: Under the Show Me toolbar, choose the text table option. As ssen, Tableau has changed the visualization from the horizontal bars to the text table.
Step 6: Drag the Measure Names from the Data pane to the Color in the Marks shelf. Tableau will add colors to the calculated fields, as shown below.
As you can observe, there are differences between the total count vs the distinct count. It is because there are duplicates in the data fields. Both the COUNTD and COUNT functions avoided the null values. However, the COUNTD function excluded duplicate values from the data field while COUNT included the duplicate values.
Example #3
In this final example, we will demonstrate another use case where you can apply the COUNTD function in Tableau. For the demonstration, we have leveraged the athelete_events dataset in Tableau. The athelete_events dataset contains historical data on the modern Olympic Games where each record corresponds to an individual athlete competing in an individual event, including the athlete’s name, sex, age, height, weight, country, and medal, and the event’s name, sport, games, year, and city.
To create a Tableau visualization using the COUNTD function, follow the below steps.
Step 1: Source the athelete_events dataset into Tableau using the File – Open.
Step 2: Navigate to a new worksheet. You can see the imported dataset under the Data pane. Right-click on the Data pane and select Create Calculated Field from the context menu.
Step 3: In the calculation field window, specify the logic using the COUNT and COUNTD functions. Here we have created 4 different measures, as shown below.
Total Athletes displays the total number of athletes who participated in the Olympic games.
Total Distinct Athletes displays the total number of unique athletes who participated in the Olympic games.
The total Sports Category displays the total number of sports in the Olympic games.
The total unique sports category displays the total number of unique sports in the Olympic games.
Step 4: Drag the newly created measures to the Columns shelf and the Games field to the Rows shelf. Tableau has created horizontal bars automatically in the view.
Step 5: Change the horizontal bars to the text tables by choosing the option under the Show Me toolbar in Tableau.
Step 6: Drag the Measure Names to the Color in the Marks shelf. You can use the Tableau visualization to identify the unique vs total values for each measure.
Important Things to Note
- Tableau Countd function excludes duplicate and null values and provides the count of a unique set of values in your data.
- This function works best with discrete data fields given they contain a finite set of values though you can use continuous fields as well.
- Consider the performance implications of using this function for very large datasets before you recommend using it in Tableau.
- There could be scenarios where Tableau countd not working. If you observe such issues, consider troubleshooting options such as accurate syntax, availability of any filters or Tableau countd blended data.
Frequently Asked Questions (FAQs)
The key difference between COUNTD and COUNT function are highlighted below:
COUNTD:
This function returns the number of distinct items in a group. This means duplicates are avoided.
For example, if [Country] contains 5 values i.e. The USA, The UK, Brazil, Brazil, (null) then this function will return 3 ignoring the null and duplicate values.
COUNT:
This function returns all the number of items. This means duplicates are included
For example, if [Country] contains 5 values i.e. The USA, The UK, Brazil, Brazil, (null) then this function will return 4 ignoring the null and duplicate values
The Countd function works with both discrete and continuous fields in Tableau. For discrete fields where the number of values is finite, the usage of this function will provide unique values for your analysis or reporting. However, for all practical purposes, it is not ideal to use this function in continuous fields given the range of unique values this function may provide.
Yes, the Tableau countd function may exhibit performance challenges, especially for very large datasets. Hence, it’s recommended that for very large datasets, consider adding system resources to avoid any potential system slowness or performance issues.
Yes, you can combine the COUNTD function with other functions or aggregations such as SUM, MIN, MAX, etc. to perform any calculations.
The COUNTD function doesn’t include any null or blank values in the data. However, if you need the nulls or blank values to be included in the count value then consider using any expressions to handle them.
Download Template
This article must help understand the Tableau COUNTD formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Tableau COUNTD. Here we explain how to use COUNTD formula with examples and points to remember. You can learn more from the following articles –
Leave a Reply