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.

powerpivotunique2013_10

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.

Or watch on YouTube: Show Distinct Count in Excel Pivot Table with PowerPivot

________________________

daxformulas01

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in PowerPivot. Bookmark the permalink.

7 Responses to Count Unique Items in Pivot Table With Excel 2013 PowerPivot

  1. drew says:

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

    • Debra says:

      @drew, that could be the problem, thanks. Hadn’t thought of that!

      • drew says:

        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. Pingback: Excel Roundup 20131202 | Contextures Blog

  3. Shair says:

    As always, great article Debra! Is there an easy way to do this with an OLAP cube either in Excel 2010 or 2013?

  4. drew says:

    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

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>