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.
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!
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.
The result shows the number of distinct stores, or unique stores, in each region and city.
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.
________________________
Could the disappearing ribbon come from 32 bit Office running on 64 bit Windows?
As ever, thanks for a great article.
@drew, that could be the problem, thanks. Hadn’t thought of that!
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 🙂
As always, great article Debra! Is there an easy way to do this with an OLAP cube either in Excel 2010 or 2013?
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 🙂
Apologies for the double post…too fat fingers for this phone
Exactly what I needed!