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 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.

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 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.

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 focus on Barry's Count of Product and Count of Customer.)

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 focus on Barry's Count of Customer and Distinct Count of Customer.)

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

[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

5 comments to Count Unique in Excel Pivot Table

  • 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.

  • Roger Govier

    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.

  • 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...

  • Roger,

    I forgot to say "Thanks!"

  • Roger Govier

    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!!!!

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>