8. Count and Count Distinct

Overview

These two count functions are used regularly in Tableau, but it's easy to make mistakes when writing formulas with these functions. In this lesson, we will count the number of customers for each product.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

Count function

- Simply returns the numeric count of records
- Is normally supplied with an expression before performing a count
- Will contain duplicates if duplicates occur in the relevant column

Count Distinct function

- Returns the unique or distinct numeric count of records
- Is normally supplied with an expression before performing a count
- Will not contain duplicates if duplicates occur in the relevant column

Transcript

In an earlier lesson, I showed a quick example of how to use count and count (distinct) in a visualization.

In this lesson, we are going to delve deeper into these very useful functions And the goal of the lesson is to count the number of customers that each product has.

Let's start by using Count And to do this, I am going to create a calculated field by right-clicking, create calculated field and I'll call it customer count And I'll use the count function and I am going to count customer ID and then press OK I'll now simply add this calculated field to columns and as you can see, we have a new bar chart with customer count now I know that in my dataset I have 122 customers and so, the customer count is clearly not working correctly These numbers are much too big and to find out why, let's go to the data source and in the data source, I can see the problem When we apply the count function to a column say customer ID, Tableau simply counts all the entries within this column and does not take into account duplication So, if a specific customerID appear multiple times Tableau will count these as individual items in its chart.

In this example, each row corresponds to the monthly sales for a specific product and a specific customer So, all of the customers will appear multiple times in the data set And so, in our chart, this explains why our numbers for customer count are so big.

To fix this problem, we need to use Count (Distinct) which ignores any duplicates So, let's go back into my formula and change the function to Count (Distinct) which is called CountD I'll then press OK, and as you can see we now have the correct value for our customer count Now let's say I want to change the calculation so that instead of getting the absolute number of customers I can see the percentage of total customers that each product serves To do this, we use a new function called total The total function queries the data source directly to return certain values based on the expression that the total function accepts Let's see it in action by creating a new calculated field called percentage of customers And I will simply take my existing formula CountD of the CustomerID and divide by the total CountD of the CusotmerID And in actual fact, I can use my existing calculated field in here which is going to be customer count And I will press OK.

And add this calculated field to my columns And as you can see, we now get a percentage of total for each product And if I sort from smallest to largest we can see the Sycalone, has 90% coverage Byresta just under 88% and Polinio 86% whereas at the bottom, for a product like Bifecta, I only have slightly more than 42% coverage Functions such as total are very useful particularly when you want to query the full data set as part of your calculation In the next lesson, I'll show you a set of functions called Window Functions that give you some further functionality when performing these tasks

>
Formulas in Tableau
Formulas in Tableau Part 1

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial