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.

countdistinct02a

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)

countdistinct10a

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

countdistinct03a

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

countdistinct04a

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

countdistinct00a

  • 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

countdistinct01a

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.

countdistinct05a

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.

countdistinct06a

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.

countdistinct07a

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.

countdistinct08a

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.

 countdistinct09a         

  • Make your changes to the formula, then click OK

___________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Calculated Field, Excel Pivot Tables. Bookmark the permalink.

13 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. 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: Create helper column that marks distinct values

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

  7. Triumph says:

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

  8. Pingback: Distinct Count in Pivot Tables – Finally in Excel 2013 » Bacon Bits:

  9. Pingback: count unique days and not rows/records.

  10. Sizor Mohanty says:

    Awesome…

  11. Pingback: Count Unique Items in Pivot Table With Excel 2013 PowerPivot

  12. 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.

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>