• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel PivotTable Distinct Count

You are here: Home / Power Pivot / Excel PivotTable Distinct Count
PivotTable Distinct Count
April 3, 2017 by Mynda Treacy

With the integration of Power Pivot into Excel 2013 (known as the Data Model), we now have access to a range of more advanced ‘Power’ tools right from within Excel.

One of these tools enables us to easily get the distinct count for a field in a PivotTable.

Note: Don’t worry if you don’t have Excel 2013 or 2016, or don’t have the Data Model (Power Pivot) in your version of Excel, I’ll show you a workaround you can use instead.

Download the workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Excel PivotTable Distinct Count Example

Let’s take the data below and say we want to know how many different products we have in each category, in other words, the distinct count of Products per Category.

Excel PivotTable distinct count example

The end result will look like this:

products by category

Step 1: Insert a PivotTable and in the ‘Create PivotTable’ dialog box check the ‘Add this to the Data Model’ box:

create PivotTable

This adds the data to Power Pivot /the Data Model.

Note: If you don’t have this check box available it means your version of Excel doesn’t include Power Pivot, but don’t worry, remember I’m going to show you a workaround.

Step 2: Build the PivotTable placing the Product field (i.e. the field you want to count) in the Values area. This will return the count of the records/transactions for the products.

Then, to display the Distinct Count right-click the values column > Value Field Settings > Summarize Values By > Distinct Count:

distinct count

Warning: If you have blank cells in the field you're counting they will also be included in the distinct count:

distinct count includes blank cells

It makes sense since blank cells are also a distinct item in your list, but I can understand that it's not always desireable to include them.

If you want to exclude blanks from your distinct count then you can either remove them from your source data, or write a Measure/Calculated Field using Power Pivot's DAX functions, like so:

=CALCULATE( DISTINCTCOUNT(Table1[Product]), NOT( ISBLANK(Table1[Product])))

This formula gets entered in the Power Pivot tab > Calculated Fields (Excel 2013) or Measures (Excel 2010 & 2016) dialog box.

It's then available in the PivotTable Fields List:

distinct count excluding blank cells

Thanks to Alex Klimson for raising this issue.

Don’t have the Data Model

If you don’t have the ‘Add this data to the Data Model’ check box available in your version of Excel, then you have two options depending on whether you’re using Excel 2007 or Excel 2010 as described in this post:

https://www.myonlinetraininghub.com/excel-pivottables-unique-count-3-ways

Want More Power

Power Pivot, or the Data Model (why it has two names is beyond me), has a load more cool features which enable you to go beyond the limitations of regular PivotTables.

To get a better understanding of what Power Pivot can do for you, or if you’d like to learn Power Pivot, please take a moment to check out my Power Pivot course.

Tip: Don’t attempt Power Pivot unless you are already proficient with PivotTables. Click here to check out our PivotTable course.

PivotTable Distinct Count

More Power Pivot Posts

CUBE Functions

Excel CUBE Functions

Excel CUBE Functions are a great alternative to GETPIVOTDATA for Power Pivot PivotTables and still work with Slicers.
Introduction to DAX

Introduction to DAX Measures

Introduction to DAX in Power BI and Excel. Where and how to write DAX measures, understanding filter context and more.

Toggle Top N with Slicers

Toggle Top N with Slicers using disconnected tables in Power Pivot. These easy measures allow users to choose their top n.
power pivot running total

Power Pivot Running Total

The defaul Power Pivot Running Total setting doesn't handle aggregated periods. This measure solves this issue.
static data tables

Static Tables in Power Query, Power Pivot and Power BI

Use static tables to store data in Power Query, Power Pivot and Power BI without needing to load data from an external source
custom formatting strings in power bi

Custom Formatting Strings in Power BI

Control how data is displayed in Power BI using your own formats. Like hiding negative or zero values, using symbols or custom number formats
GETPIVOTDATA Function for Power Pivot

GETPIVOTDATA Function for Power Pivot

The GETPIVOTDATA Function for Power Pivot references measures and so it works a little different to GETPIVOTDATA for regular PivotTables.
values_as_%_of_another_column_thumb

Show Values as Percentage of Another PivotTable Column Total

Show Values as % of Another PivotTable Column Total can't be done with regular PivotTables, however with Power Pivot we can use a DAX measure.
Change PivotTable Aggregation Methods using Excel Slicers

Change PivotChart Aggregation Methods using Excel Slicers

Change Pivot Chart Aggregation Methods using Excel Slicers and Power Pivot. Disconnected tables and the SWITCH function are the secret sauce.
Power Pivot does VLOOKUP

Power Pivot does VLOOKUP

Creating PivotTables from multiple tables used to require consolidating the data into one table, but now Power Pivot does VLOOKUP, kind of. So you don't need to.




Category: Power Pivot
Previous Post:Excel TEXTJOIN Function
Next Post:Power Query ListsPower Query Lists

Reader Interactions

Comments

  1. Shine

    November 21, 2021 at 9:19 pm

    Hi i am using Microsoft excel 2013 and i am using distinct count… how to exclude blank cells value from distinct count in pivot table ?

    Reply
    • Mynda Treacy

      November 22, 2021 at 12:34 pm

      You will need to write a DAX measure for this. For example, to ignore blank products your formula would be:

      =CALCULATE( DISTINCTCOUNT(Table1[Product]), Table1[Product] BLANK())

      Reply
  2. Alice

    June 21, 2021 at 11:37 pm

    I receive “Calculation error in measure ‘Range'[Calculated field 1]: A function ‘CALCULATE’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.” when I use the function for blanks not to be counted. Am I doing something wrong?

    Reply
    • Mynda Treacy

      June 22, 2021 at 12:21 pm

      Hi Alice, Please post your question on our Excel forum where you can also upload a sample file or screenshots and we can help you further.

      Reply
  3. SarahN

    April 26, 2018 at 12:56 am

    Hi. Could anyone please help on this topic where i have added 15 pivots already without ticking the option “add this to data model” while making the pivots, however now i need a distinct count and that is possible through ticking it before making the pivot.
    How can i go back and tick this option to get the distinct count option on all 15 pivots? I cannot redo all the pivots since its too tedious

    Reply
    • Mynda Treacy

      April 26, 2018 at 10:00 am

      Hi Sarah,

      You can go to your source data > Insert tab > PivotTable and check the ‘add to data model’ option. However I recommend you create them again otherwise you will have the data in your Excel file 3 times:

      – Once in the worksheet i.e. the source data
      – Once in the regular PivotTable pivot cache
      – and once in the Power Pivot/Data Model.

      Also, if you have some PivotTables connected to the Data Model and others connected to the Pivot cache then you can’t use Slicers to control those different PivotTables.

      Mynda

      Reply
  4. KC

    March 12, 2018 at 6:40 am

    Thank you for this tutorial. I just discovered your website and it is amazing how and why I hadn’t heard of you before. Now you are saved in my favorites and anywhere I can save you on my devices.

    I used the PowerPivot distinct count however, my data consist of 2016 and 2017 products which means there are duplicate product names. But distinct recognizes only 1 and counts only 1 for both dates. any work around to this?

    Reply
    • Mynda Treacy

      March 13, 2018 at 3:54 pm

      Hi KC,

      If you want to see the distinct count at a year level then you need to add the Year field to your PivotTable rows/columns area so that Excel knows you want the distinct count by product within each year.

      If you’re still stuck please post your question in our Excel forum with a sample Excel file so we can help you further.

      Mynda

      Reply
  5. Alex

    April 10, 2017 at 10:18 am

    Hi Mynda,

    Thank you for this post.

    I believe the case when your data has BLANK cells deserves special attention. What surprised me is that the blank cells are also counted. For example, when your column has {1, 2, (blank)} entries DISTINCTCOUNT returns 3, while COUNT—just 2. I wish Microsoft made a provision to exclude blank cells in distinct counting. So, beware of traps.

    Reply
    • Mynda Treacy

      April 10, 2017 at 11:36 am

      Hi Alex,

      Distinct Count counts the different types of values in a field. If you have blank cells then this is also a type of value, albeit a blank, and therefore it is included in the distinct count.

      Mynda

      Reply
  6. Gulraiz A. Qureshi

    April 5, 2017 at 8:54 pm

    I think Pivot Tables are not for me. I receive quotations from 2-3 different suppliers around the world with sometimes hundreds of items included. All items have a different description, different part number, different substitute part number, quantity (some time same), different Price and different totals. How can I benefit from Pivot Tables.

    Furthermore, I receive price of different flight checks from different companies, or price for various trainings from different companies. I tried Pivot Tables but no results to my usage.

    Can you advise how can I benefit from Pivot Tables?

    Reply
    • Mynda Treacy

      April 6, 2017 at 8:48 am

      Hi Gulraiz,

      It depends what you want to achieve. PivotTables may not be the right tool for the job.

      If you have data in multiple tables with different codes, descriptions etc. for the same items, then that is a data problem. You need to create some form of mapping table that maps the different codes etc. from each supplier into one common code so you can then compare apples with apples.

      You would have to build that mapping table yourself. There’s no automated tool to do that for you.

      Mynda

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.