Count Unique in Excel Pivot Table

In a previous article,  Roger Govier showed us a couple of different ways to get a Unique count with a Pivot Table.

  • Create a Pivot of a Pivot Tables results (fast)
  • Add a calculated column to the source data (much slower)

Now Roger has added another technique that you can use, in Excel 2013 and later. I’ll hand things over to Roger, so he can describe the steps.

Distinct Count in Pivot Table

Add to Data Model

With the advances made by Microsoft with Power Pivot, and DAX (Data Expressions) formulae, which are more powerful than the inbuilt Excel functions, there is a way of obtaining a Distinct Count. From Excel 2013 onward, when creating a Pivot Table form any set of data, there is an option to Add this data to Data Model.

rjg_distinctcount01

When you select this option, Excel treats the data as being loaded into Power Pivot, as opposed into the older Pivot Table.

My Default Choice

Even though you might not want to be combining data from multiple tables and creating relationships between tables, this is now my default choice when I create a Pivot Table.

Then, when creating the PT, and dragging an item to the Values area, when you click on Value Field settings, and scroll to the bottom, there is a new measure that has been added called Distinct Count.

rjg_distinctcount02

See a Distinct Count

Choosing this Measure, as opposed to Count, does indeed give you a unique count as can be seen in the following PT example.

For each Salesperson, I allocated Product to the Values area twice, once as Count and once as Distinct Count, and I did the same for Customers.

NOTE: Distinct Count of Product is a measure you wouldn’t need, as it clearly has to be the same as the number of Products sold, 5. It has only been included for illustrative purposes.

rjg_distinctcount03

Count of Items

As you can see the Count of Product is the same as the Count of Customer, which is not at all surprising as it is the count of all of the transactions made by these particular Salespeople.

(The image is trimmed below, to show Barry’s Count of Product and Count of Customer, at a larger font size.)

rjg_distinctcount05

Distinct Count of Customer

But, the Distinct Count of Customer shows that even though there were 41, 42 and 27 transactions by the three Salespeople shown, they made those sales to 11, 12 and 13 Unique Customers in each case, showing there have been multiple sales to the same Customer.

(The image is trimmed below, to show Barry’s Count of Customer and Distinct Count of Customer, at a larger font size.)

rjg_distinctcount04

Use This Method

This is a much easier method than those I have shown in earlier articles, so if you have Excel 2013 or 2016, or Office 365, then this is definitely the way to go. So, even if you don’t need all of the other advantages of Power Pivot, it is always worth choosing the option to add data to the Data Model for all of the new Pivot Tables that you create.

Big thanks to Microsoft for introducing this long awaited feature, and for making it so easy for people to use without having to get into learning DAX (although there are many benefits from doing so).

Get the Sample File

Thanks to Roger, for explaining how we can easily get a distinct count now! It’s easy to overlook that “Add this data to the Data Model” check box, when creating a pivot table. Or, if you aren’t aware of the benefits, you wouldn’t bother to check it.

If you’d like to download Roger’s sample file, from his earlier tutorial, you can go to my Contextures website. Visit the different ways to get a Unique count page, and go to the Download the sample file section. You can use that data to create a new pivot table, and remember to check the “Add this data to the Data Model” box!

______________

Distinct Count in Pivot Table