COUNT and COUNTD Functions in Tableau – Complete Detailed Notes

Course: Tableau Data Visualization and Analytics

Topic: Counting Records, Counting Non-Null Values, and Finding Unique Values

Learning Objective: Understand the difference between COUNT and COUNTD, create calculated fields, analyze records and unique customers, and build practical Tableau dashboards using counting functions.

1. Introduction to COUNT and COUNTD Functions

Tableau provides aggregate functions to summarize data and change the level of detail displayed in a visualization. COUNT and COUNTD are two important functions used to measure the number of values or unique items in a dataset.

• COUNT: Counts non-null values in a field.
• COUNTD: Counts distinct, or unique, non-null values in a field.

For example, an online store may contain 100 order records but only 65 unique customers. COUNT can count the non-null customer ID entries, while COUNTD can calculate how many different customers appear in the data.

Both functions ignore NULL values. The result depends on the field selected and the level of detail of the view.

2. Understanding the COUNT Function

2.1 Definition

The COUNT function returns the number of non-null items in a specified expression. Repeated values are counted separately.

2.2 Syntax

COUNT(expression)

Expression: The field or expression whose non-null values you want to count.

2.3 Example

Consider the following customer dataset:

Create a calculated field named Total Customer ID Entries:

COUNT([Customer ID])

Result: 5, because five records have a non-null Customer ID. Repeated IDs are counted more than once, and the NULL ID is excluded.

2.4 How to Create a COUNT Calculated Field

1. Open Tableau Desktop or Tableau Cloud authoring.
2. Connect to a dataset containing the fields you want to analyze.
3. In the Data pane, right-click and select Create Calculated Field.
4. Enter a name such as Total Customer ID Entries.
5. Enter the formula COUNT([Customer ID]).
6. Click Apply or OK after validating the calculation.
7. Drag the calculated field to Text on the Marks card to display the result, or use it in a chart.

2.5 Common Uses of COUNT

• Counting non-null customer IDs.
• Counting records with a populated order number.
• Counting transactions that contain a sales value.
• Counting product names or employee IDs that are present.
• Comparing populated records across regions, categories, or time periods.

3. Understanding the COUNTD Function

3.1 Definition

COUNTD returns the number of distinct, non-null values in an expression. If a value appears multiple times, it is counted only once within the relevant group.

3.2 Syntax

COUNTD(expression)

3.3 Example

Using the same Customer ID dataset:

COUNTD([Customer ID])

Result: 3, because the distinct non-null customer IDs are C101, C102, and C103.

3.4 How to Create a COUNTD Calculated Field

1. Open a Tableau worksheet.
2. Right-click in the Data pane and choose Create Calculated Field.
3. Name the field Unique Customers.
4. Enter the formula COUNTD([Customer ID]).
5. Validate and save the calculation.
6. Drag Unique Customers to Text to display the number, or add it to a chart to compare unique customers by region or category.

3.5 Common Uses of COUNTD

• Calculating unique customers.
• Counting distinct order IDs.
• Finding the number of different products sold.
• Measuring unique website visitors when the dataset supports that interpretation.
• Counting distinct employees, suppliers, cities, or locations.

4. Difference Between COUNT and COUNTD

Important: COUNT does not automatically mean the number of physical rows in the source. It counts non-null values of the expression. If you need to count records, choose a field that is populated for every record or use an appropriate row-counting approach for your data source.

5. COUNT and COUNTD: Step-by-Step Calculation

Suppose an order table contains these Order IDs:

Calculations:

COUNT([Order ID])

Result: 6 non-null Order ID entries.

COUNTD([Order ID])

Result: 4 unique non-null Order IDs: O101, O102, O103, and O104.

The difference occurs because some Order IDs appear in more than one row. For example, an order containing several products can have multiple detail records. COUNTD([Order ID]) can count distinct orders, while COUNT([Order ID]) counts the populated order ID entries in the rows represented by the calculation.

6. Using COUNT and COUNTD in Visualizations

6.1 Display a Single Count as a KPI

1. Create a calculated field such as COUNT([Order ID]).
2. Drag the calculated field to Text on the Marks card.
3. Increase the text size and format the number.
4. Add a descriptive title such as Order ID Entries.

6.2 Compare Unique Customers by Region

1. Drag Region to Columns.
2. Drag Customer ID to Rows or Text.
3. Change the aggregation to Count (Distinct), or use a calculated field with COUNTD([Customer ID]).
4. Use a bar chart to compare distinct customers across regions.

6.3 Count Orders by Month

1. Drag Order Date to Columns.
2. Choose a suitable date level, such as Month.
3. Drag Order ID to Rows or Text.
4. Use COUNTD when you want distinct orders per month, especially if an order may have multiple detail rows.
5. Use COUNT when you specifically want to count non-null Order ID entries.

6.4 Create a Product Variety Chart

1. Drag Category to Rows.
2. Drag Product ID to Columns.
3. Use COUNTD([Product ID]) to show how many unique products appear in each category.
4. Sort the bars to highlight categories with the most product variety.

7. Practical Calculated Field Examples

Example 1: Count Populated Order IDs

COUNT([Order ID])

Counts all non-null Order ID entries represented by the calculation.

Example 2: Count Unique Orders

COUNTD([Order ID])

Counts distinct non-null order identifiers.

Example 3: Count Unique Customers

COUNTD([Customer ID])

Useful for customer reach or customer distribution analysis, provided Customer ID identifies customers consistently.

Example 4: Count Populated Product Names

COUNT([Product Name])

Counts non-null Product Name entries, including repeated product names.

Example 5: Count Distinct Product Names

COUNTD([Product Name])

Counts unique non-null product name values. If different products share the same name, consider using Product ID instead.

Example 6: Count Unique Cities

COUNTD([City])

Counts distinct non-null city values. Check spelling variations such as "Mumbai" and "mumbai" in your data if they should represent the same city.

Example 7: Count Non-Null Sales Values

COUNT([Sales])

Counts rows with a non-null Sales value; it does not calculate the total sales amount. Use SUM([Sales]) for total sales.

Example 8: Count Unique Employees

COUNTD([Employee ID])

Counts distinct employee IDs in the current level of detail.

8. Understanding NULL Values

A NULL value represents missing or unknown data. COUNT and COUNTD exclude NULL values from their results.

If you need to count records where a field is NULL, COUNT and COUNTD on that field will not do so directly. You can create a conditional calculation that returns 1 for NULL values and then aggregate it, for example:

SUM(
    IF ISNULL([Customer ID]) THEN 1
    ELSE 0
    END
)

This calculation counts rows in which Customer ID is NULL, assuming each source row is represented once at the calculation's level of detail. Data relationships, joins, and visualization granularity can affect the result.

9. COUNT and COUNTD with Filters and Groups

Both functions calculate results for the data included in the relevant calculation and view. Adding Region, Category, or Month to a visualization divides the results into groups. Filters can change which records contribute to those results.

• By Region: COUNTD(Customer ID) calculates distinct customer IDs for each region.
• By Category: COUNTD(Product ID) calculates distinct products for each category.
• By Month: COUNTD(Order ID) calculates distinct order IDs for each month.
• With a filter: A Region filter can limit the calculation to selected regions, subject to Tableau's filter order of operations.

Important: A distinct count calculated separately in multiple groups may not add up to the overall distinct count. For example, a customer who purchases in two regions can be counted once in each region but only once in an overall COUNTD(Customer ID) result.

10. Common Mistakes and How to Avoid Them

11. Practical Project: Sales and Customer Analysis Dashboard

Project Goal: Build a Tableau dashboard showing order entries, unique orders, unique customers, and product variety.

Dataset Fields

• Order ID
• Customer ID
• Order Date
• Region
• Category
• Product ID
• Sales

Calculated Fields

// Order ID Entries
COUNT([Order ID])

// Unique Orders
COUNTD([Order ID])

// Unique Customers
COUNTD([Customer ID])

// Unique Products
COUNTD([Product ID])

// Populated Sales Entries
COUNT([Sales])

// Total Sales
SUM([Sales])

Dashboard Building Steps

1. Create separate KPI worksheets for Order ID Entries, Unique Orders, Unique Customers, and Unique Products.
2. Create a bar chart comparing unique customers by Region.
3. Create a monthly trend chart using COUNTD(Order ID).
4. Create a category chart using COUNTD(Product ID).
5. Add Region, Category, and Order Date filters.
6. Arrange the KPI cards and charts into a dashboard.
7. Use clear titles and number formats so users can distinguish entries from unique entities.

Expected Business Insights

• Identify regions with the most unique customers.
• Compare the number of order entries with unique orders to spot repeated order IDs.
• Understand which categories contain the widest variety of products.
• Monitor monthly order activity and customer reach.
• Investigate missing identifiers when counts are unexpectedly low.

12. Exercises for Practice

Exercise 1: A field contains values A, B, A, C, B, D. Calculate COUNT and COUNTD.

Exercise 2: An Order ID field contains O1, O2, O1, O3, NULL, O2. Write formulas for order ID entries and unique orders.

Exercise 3: Create a calculated field that counts distinct Customer IDs by Region.

Exercise 4: Explain why the sum of distinct customers across three regions may be greater than the overall distinct customer count.

Exercise 5: Create a dashboard with unique customers, unique orders, and unique products as KPI cards.

Exercise Answers

1. COUNT = 6; COUNTD = 4.
2. COUNT([Order ID]) returns 5; COUNTD([Order ID]) returns 3.
3. Use COUNTD([Customer ID]) and place Region in the view to calculate the distinct count per region.
4. The same customer may appear in more than one region, so they can be counted once per region but once overall.
5. Create one calculated field for each distinct entity, display each on Text in a separate worksheet, and combine the worksheets in a dashboard.

13. Tableau Interview Questions and Answers

Q1. What is the COUNT function in Tableau?

Answer: COUNT returns the number of non-null items in an expression. Repeated values are counted each time they occur.

Q2. What is the COUNTD function?

Answer: COUNTD returns the number of distinct, non-null values in an expression. Each unique value is counted once within the relevant group.

Q3. What is the main difference between COUNT and COUNTD?

Answer: COUNT counts all non-null occurrences, while COUNTD counts distinct non-null values.

Q4. Do COUNT and COUNTD include NULL values?

Answer: No. Both functions ignore NULL values.

Q5. How do you count unique customers?

Answer: Use COUNTD([Customer ID]), assuming Customer ID uniquely identifies each customer.

Q6. How do you count unique orders when an order has several line items?

Answer: Use COUNTD([Order ID]) to count distinct order identifiers.

Q7. Does COUNT([Sales]) return total sales?

Answer: No. It counts non-null Sales entries. Use SUM([Sales]) to calculate total sales.

Q8. Why might COUNT and COUNTD return the same result?

Answer: They can match when every non-null value in the field is unique and there are no additional repeated non-null values.

Q9. Can distinct counts be added to calculate an overall total?

Answer: Not always. If the same entity appears in multiple groups, adding group-level distinct counts double-counts that entity.

Q10. Which function should be used to count distinct products in each category?

Answer: Use COUNTD([Product ID]) and place Category in the view.

14. Quick Revision Checklist

• Understand COUNT and COUNTD syntax.
• Remember that both functions ignore NULL values.
• Use COUNT to count non-null occurrences.
• Use COUNTD to count distinct non-null values.
• Use a stable identifier when counting unique entities.
• Distinguish a count of values from a sum of numeric values.
• Check how filters and the level of detail affect results.
• Avoid adding group-level distinct counts without checking for overlap.
• Practice creating KPI cards and grouped bar charts.

15. Conclusion

COUNT and COUNTD are essential Tableau aggregation functions for understanding record entries and unique entities. COUNT is appropriate when you need the number of non-null occurrences in a field, whereas COUNTD is appropriate when you need the number of distinct non-null values. Choosing the correct function helps produce accurate customer, order, product, and operational reports.

16. Learn Tableau with JustAcademy

Explore the Tableau training course and learn data visualization, calculations, aggregation functions, filters, dashboards, and reporting techniques.

• JustAcademy Tableau Training
• Register for a Tableau Course Demo

Reference: Official Tableau Documentation – Aggregate Functions