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.
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.
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 if you don’t need to combine data from multiple tables and create relationships between tables, this is now my default choice when I create a Pivot Table.
Then, after you add an item to the Pivot Table’s Values area, you can click on Value Field settings. Scroll to the bottom of the list, and there is a new measure that has been added called Distinct Count.
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 has only been included for illustrative purposes. It is a measure you wouldn’t need, as it clearly has to be the same as the number of Products sold, 5.
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 focus on Barry’s Count of Product and Count of Customer.)
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 focus on Barry’s Count of Customer and Distinct Count of Customer.)
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
[Debra here again] 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!