Count Unique Items in Pivot Table With Excel 2013 PowerPivot

Count Unique Items in Pivot Table With Excel 2013 PowerPivot

Last year, I described how to create a unique count in an Excel 2010 pivot table, by using the PowerPivot add-in. In that version, you can right-click on the table name in the field list, to add a new measure.

countdistinct00a

Missing PowerPivot Tab

I decided to make a video to show the steps, using Excel 2013, instead of Excel 2010. Things didn’t go too well, at first. For unknown reasons, the PowerPivot tab wasn’t showing up on the Excel 2013 Ribbon, even though the add-in was installed, and checked off as active.

A long and frustrating Google search didn’t turn up any solutions, at first. Many articles said the add-in had to be checked in the list of COM add-ins. That didn’t help, because it was checked.

Finally, I stumbled onto this Microsoft article that suggested a change to the Windows Registry. The instructions are in the last section – “Troubleshooting: Power Pivot ribbon disappears”.

As suggested, I deleted the PowerPivotExcelAddin item in the User Settings section in the registry, but didn’t see a PowerPivotExcelClientAddIn.NativeEntry.1 item in the Addins section, so I skipped that step. Fortunately, that worked, and the PowerPivot tab showed up again, in Excel 2013.

Connect to the Data

Once the PowerPivot tab was visible, I connected to the Excel file that had the sample data, without any problems. The next step was to build a pivot table in the PowerPivot window, and that went well too.

In the pivot table, I right-clicked on the table name, to add the new measure, but that command didn’t appear. Uh-oh!

powerpivotunique2010_14

Create a Unique Count in Excel 2013

Fortunately, I have a copy of Rob Collie’s book, DAX Formulas for PowerPivot. The book’s “Add a Measure” section showed a screen shot from Excel 2013, where the name has changed to Calculated Fields.

So, I used the New Calculated Field command on the Ribbon, and created a formula, using the DISTINCTCOUNT function.

powerpivotunique2013_11

The result shows the number of distinct stores, or unique stores, in each region and city.

powerpivotunique2013_15

Video: Show Distinct Count in Excel 2013 With PowerPivot

To see the steps for setting up the pivot table, and creating the formula, please watch this video.

Video: Count Unique in Excel Pivot Table

In this short video, I show how to count unique items (distinct count) in a Microsoft Excel pivot table. This method works in Excel 2013 and later versions.

________________________

10 thoughts on “Count Unique Items in Pivot Table With Excel 2013 PowerPivot”

  1. Could the disappearing ribbon come from 32 bit Office running on 64 bit Windows?
    As ever, thanks for a great article.

      1. Though I’d love to take credit for it, it was raised as a possibility to me by Bill Pearson at the Tampa BI SQLSaturday precon, where my project evaporated! I have since moved everything to 64 bit (but had to buy Office twice!), so the observation belongs to Bill, but I am happy to carry the news 🙂

  2. Though I’d love to take credit for it, it was raised as a possibility to me by Bill Pearson at the Tampa BI SQLSaturday precon, where my project evaporated! I have since moved everything to 64 bit (but had to buy Office twice!), so the observation belongs to Bill, but I am happy to carry the news 🙂

Leave a Reply to Shair Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.