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.


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!


Distinct Count in Pivot Table

You may also like...

12 Responses

  1. Doug Glancy says:

    Debra (and Roger), this is a great feature. I used it just the other day. I counted the same field, one time distinct and the other not. What I’d like to know is a good way to then show the ratio of the two. My first thought was to use a calculated field (never my favorite option) but they weren’t available.

    Which brings up one other issue: I understand the using the data model turns off some features. I think Grouping is one. I tried to group my days in the above-mentioned report into months, but grouping wasn’t available. This is not a big deal, as I often end up creating a “Year-Month” field in my source data to get around the way pivot grouping lumps together months from different years if you’re not careful.

    If you have an answer to the ratio question above I’d love to hear it.

  2. Roger Govier says:

    Hi Doug
    Sorry to be a while getting back to you.
    You can use Calculated Fields (Measures) when you use the Data Model, but to get to them you need to first click on the Power Pivot Tab.
    Then you will see a section for Calculated Fields.
    Create a new one, give it whatever name you wish.
    Then in the formula area use the auto complete feature to generate the formula.
    In my case it was =C which then allowed me to select Counta( then T, which brought up a list and I was able to select Table7[Customer] as the name of the source field
    I went on to complete the formula as
    = Counta(Table7[Customer]) / DistinctCount(Table7[Customer])
    REMEMBER to click the option for you to Check the formula and as long as you don’t get any errors you are good to go.
    Go back to the PT, Refresh and the new measure will appear, and you can drag it to the PT.

    Yes, you are right about Grouping dates.
    But again, if you Click on Manage Data Model in the Power Pivot Tab, you will see you can add a Calculated Column
    Type in your formula like = FORMAT(Date,”yy-mmm”)
    and then you will see your new column in your PT on refresh.

    Hope this helps.

  3. Doug Glancy says:

    Hi Roger,

    Sweet! I followed your steps above and got my Ratio field, which I can now chart just like another field.

    I think the most general lesson here is that checking the Data Model box means that I’m turning my regular pivot into a Power Pivot, and that this creates a lot of opportunities. I think this may be the beginning of a beautiful friendship…

  4. Doug Glancy says:


    I forgot to say “Thanks!”

  5. Roger Govier says:

    No probs, Doug.
    Glad you got it working the way you want.
    Yep, there certainly are a whole heap of nice new features within the Power Pivot scenario.
    Still learning these myself!!!!

  6. dave says:

    Hi Roger, i have just installed excel 2016 and the distinct count option does not appear. did excel remove it from the 2016 version?

  7. Ben says:


    How did you figure it out?

  8. Teaca says:


    I have 2016 and try as I may to get the Data Model option to be activated it is still grey out. What setting are necessary for it work

  9. Chris says:

    Is there a way to add pivot table to data model after it has already been created?

Leave a Reply

Your email address will not be published. Required fields are marked *