How to Remove Duplicates in Tableau

Recently, while I was creating a report on sales data in Tableau, I noticed that a few records were repeated and being counted multiple times. Due to this, the totals in the reports were also calculated with these duplicate values.

As a solution for this, I came to know that in Tableau, there are various methods through which we can remove duplicates, such as formatting the data source details and using calculated fields.

In this Tableau tutorial, I will explain the various methods to remove duplicates in Tableau.

Table of Contents

Remove Duplicate Values in Tableau

In Tableau, there are the following methods through which we can remove duplicate values.

Remove Duplicates from the data source in Tableau
Use LOD Expression to remove duplicate values in Tableau
Use the COUNTD() function for unique counts excluding duplicates
Aggregate data to avoid displaying duplicate records

Remove Duplicates from the Data Source in Tableau

In this method, we will edit the records in the data source file, which can be an Excel or Google Sheet. This method is helpful when you want to remove duplicates from data before connecting Tableau to the dataset.

Open the data source file in Excel format.
Select all rows -> Go to Data -> Remove Duplicates icon.

Select the checkboxes for the Columns from which you want to find and delete the duplicates. After selecting the Columns, click OK.

As we click OK, the duplicate records will be removed from the data sheet.

Now, we can connect and use the data in Tableau for data visualisation.

This way, we can remove the duplicates directly from the data source and use the file later in Tableau.

Use LOD Expression to Remove Duplicate Values in Tableau

If duplicates cannot be removed in the source due to permissions, we can use Level of Detail (LOD) expressions to remove them inside Tableau.

For example, we have multiple transactions per customer, but we want to count each customer only once.

Now, follow the steps below to remove the duplicate in the Tableau data source using the LOD expression.

To create a calculated field, click on the Analysis tab and select Create Calculated Field.

Enter the formula below in the calculated field to count the distinct values (avoiding duplicates).

{ FIXED [Customer ID] : MIN([Customer Name])}

Now, to create the view, add the Customer Name and calculated field to Rows, Order ID and Product to the Text or Label card.

Unique count for removing duplicate values in tableau

In the text table, we can see that each customer will show a unique number of orders, even if there are duplicate Order IDs in the data.

Use the COUNTD() Function for Unique Counts

Another way to avoid duplicate records in the Tableau data visualisation is using the COUNTD() function. This will count only the distinct values and avoid displaying the duplicates.

To create the view, add the Region dimension to the Rows.

Create two formula fields to count the total and the unique count of the customers.

//Total Customers Count
COUNT([Customer ID])

//Unique Customers Count
COUNTD([Customer ID])

Now add both fields on the Columns shelf or side by side on the Text card in the Marks section.

Now, when we hover over the regions in the table, it will show the total customers and the distinct count of customers.

Use Distinct count to avoid duplicated in Tableau

This distinct count shows the number of customers by excluding the count of customers that had repeated purchases.

This way, we can use the COUNTD() function for the distinct count and show the records without duplicate or repeated values.

Aggregate Data to Avoid Displaying Duplicate Records

In this method, we will use the aggregate method to avoid showing duplicate records.

To create the view, add Region and Customer Name to Rows.

After this, add the Sales to Text card in the Marks section. With this, you may notice higher totals because of duplicate orders.

Now, click on the Analysis tab and ensure that Aggregate Measures. After this, select Totals -> Show Row Grand Totals.

If you don’t see any change in your totals after enabling “Aggregate Measures“, it means your dataset doesn’t contain exact duplicates in the fields being displayed. In Tableau, Aggregation only affects records that are identical across all grouped dimensions.

This way, we can aggregate the data to avoid displaying duplicate records in Tableau.

Conclusion

In this Tableau tutorial, we have learned how to handle and remove duplicate records in Tableau to ensure accurate data analysis. In the above example, we have learned to remove duplicate data at the source level by using Tableau’s built-in calculation and aggregation methods.

If you can edit the data file, removing duplicates directly in Excel or Google Sheets is the simplest approach. When working with connected datasets or restricted sources, using LOD Expressions, COUNTD(), or Aggregation inside Tableau is a preferable solution.

Remove Duplicates in Tableau