Unique Count in Excel Pivot Table With PowerPivot

In Excel, this is no built-in summary function that calculates a distinct count or unique count. In the pivot table shown below, we’d like to see the number of stores in Boston, where each product category was sold.

Instead of a unique count, the pivot table is counting each record that has a store number. So, the result is really a count of the orders, not a count of the unique stores.

Pivot Table Data Workaround

As a workaround, you can add a column to the pivot table source data, and use a formula to calculate one or zero in each row. In the screen shot below, I used the COUNTIFS function, which is available in Excel 2007, and later versions. In earlier versions, you can use SUMPRODUCT to create a unique count formula.

With this formula, we count the instances where the Order Date matches the current row date, and the Store number matches the current row number, starting from row 1, and down to the current row.

=IF(COUNTIFS(B\$1:B2,B2,E\$1:E2,E2)=1,1,0)

When we add this new field, StoreDate, to the pivot table values area, it shows the correct store count, for the selected date.

However, if we select more than one date, the StoreDate field no longer shows the correct number of unique stores.

Unique Count in PowerPivot Pivot Table

If you are using Excel 2010, you can calculate a unique count by using the PowerPivot add-in.

• In a blank workbook, use PowerPivot to connect to the workbook that contains your data, and build a pivot table.

Next, you’ll add a new measure to the pivot table, similar to a calculated field in an Excel pivot table.

• Select a cell in the pivot table
• Then, in the PowerPivot Field List, right-click on the table name, and click Add New Measure

• Type a name for the measure that you are creating. In this example, the measure is named DistinctStores
• Type the formula, to count the rows with a distinct store number. Use the table name and field name – FoodSales and Store in this example
• =COUNTROWS( DISTINCT( FoodSales[Store] ) )
• Click OK, to close the Measure Settings dialog box

The new measure is automatically added to the PowerPivot pivot table. In the screenshot below, you can see that it is correctly calculating one store in Boston. The StoreCount field is showing 7, which is a count of the orders, not the stores.

Change the Pivot Table

Even if we select multiple dates in the Report Filter, the Distinct Stores field continues to show the correct unique store count.

With the distinct count formula, you can even change the layout, and the results are automatically updated.

In the screenshot below, the product category has been moved to the Rows area, and you can see the number of unique stores where Bars were sold.

In another layout change, the Store number field is removed from the Rows area, and the unique count is still correct, showing 5 stores selling Bars.

The Grand Total row shows that there were 6 unique stores overall.

Change the Measure Formula

If you have a problem with the Measure formula, you can go back and change it.

• In the PowerPivot Field List, right-click on the Measure name, and click Edit Formula.

• Make your changes to the formula, then click OK

___________________

This entry was posted in PowerPivot. Bookmark the permalink.

20 Responses to Unique Count in Excel Pivot Table With PowerPivot

1. Chris Webb says:

Incidentally, in PowerPivot V2.0 there’s a DistinctCount function that does the same thing, so there’s no need to use CountRows(Distinct()): http://technet.microsoft.com/en-us/library/gg492142(v=sql.110).aspx

2. KMS says:

Unfortunately, you didn’t show how the fomula changes going down the column. So, I can’t figure out why your example doesn’t work.

3. Outsource Excel says:

KMS, you just copy the formula from the first cell you put it into, all the way down the column. Excel will automatically update the references for you.

4. usha says:

Row labels will have 3 fields but i need to move to columnwise …is it possible in xlsx

5. Pingback: Count if AND then Unique - Page 2

6. Triumph says:

Thanks a million for this!!!…the formula worked#Appreciative

7. Sizor Mohanty says:

Awesome…

8. Jen says:

Is there any way to do get a unique count without Excel 2013, *without* powerpivot add-in, and without the source data in the book?
I use external data sources and create pivot tables directly from the connection, so there is no table (in the workbook) for me to add a column to. We use this method because we are pivoting 250,000 – 600,000 rows of data at a time.

• diopol says:

Yes, you can create an OLAP source (with SQL Server Analysis Services or other providers) and bind your pivot table to it. Most of them support Distinct Count measures and dozen of other useful features. Though this requires additional setup, perhaps quite complex.

• simon says: