How to use Count and Count Distinct in Tableau
Count and Count Distinct are aggregated functions in Tableau. And just like other aggregate functions – they are used to perform calculations on a set of values to return a single value.
Count - whose syntax is COUNT (expression) - this function returns the number of items in a group. Note, NULL values are not counted.
Count Distinct – whose syntax is COUNTD (expression) - this function returns the number of distinct items in a group. Each unique value is only counted once. Note, NULL values are not counted.
Example of Tableau COUNT function
Let’s say, using the Sample-Superstore data set – you would like to compute the total orders per region. You can achieve that using the COUNT function.
Where in this case, you’re going to create a calculated field called total orders as shown below.
(The above calculation returns the number of orders – by basically counting every single Order_ID in this field).
And you can answer the above question by adding Region to the rows and Total orders to the text.
From the resulting view, you can see the total orders per region.
Note: The same results could be arrived at by right-dragging Order_ID to the text shelf (which populates a menu where you can choose COUNT function instead of creating the calculated field.)
Example of Tableau COUNTD Function
Let’s say, using Sample-Superstore data – you would like to compute the number of customers who bought into Phone sub-category. You can achieve that using COUNTD function.
Where in this case, you’re going to create a calculated field called ‘Unique customers’ as shown below.
(Since each customer is assigned a unique id, the above calculation will return the number of unique customers – by basically counting the unique customers ID’s, that is – each Customer ID is counted once no matter the number of appearances or purchases).
And to answer the question – I will drag Sub-category to the rows and Unique customers to the text.
From the above view – you can tell that 511 customer’s bought into phone sub-category.
Note, if we could have used COUNT (Customer ID) instead – this figure changes to 889.
And the reason is because – the function COUNT, counts all the Customer ID’s – meaning for customers who made several purchases in phone category will be counted severally, while for the case of COUNTD the function only counts the unique Customer ID’s – meaning all customers are counted once regardless of the number of purchases.
I hope this was helpful to you. To receive more of the Tableau tips, kindly join our mailing list by subscribing below.
Thank you for reading.