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.
Or watch on YouTube: Show Distinct Count in Excel Pivot Table with PowerPivot