SUM and COUNT are the most common methods of summarizing data. It is easily done in Pivot Table or any other analytical tool. What is equally important is DISTINCT COUNT. But it is not commonly used. Why not? Firstly, due to lack of awareness and secondly, due to lack of that feature in Pivot Table. Let us solve both problems in the next 10 minutes.
Contents
The Need
Count of Product in the pivot table above does NOT give count of product. It actually counts the ROWS in which product is mentioned. If there was a missing product in one of the rows, it will not be counted. Therefore, it is more like count of transactions rather than count of products sold.
What we want to know that only 3 products were sold This information is as useful as knowing that we had a total of 6 transactions.
Unfortunately, many of us have never noticed this need. This is called Unique Count or Distinct Count. Till Office 2007, Distinct Count was not directly available in Pivot Table summary options.
Since 2010 Distinct Count is added. A new item was NOT added in the menu above. It does require little more effort…
How to get Distinct Count
This feature was added to Power Pivot – Excel 2010 onwards. For Power Pivot to work, the data must be in the Data Model (the database which lives inside Excel).
Here are the steps. You can download the sample file and follow along.
First step is to add the data into Data Model. Click inside the data (which must be a Table) and open the Power Pivot menu. Choose Add to Data Model option. Now the data model will open and show you the same data.
Data Model is shown in the Power Pivot window which is separate from the base Excel window. Now click Pivot Table in the Home tab of Power Pivot.
The newly created table is a Power Pivot Table. Now drag drop Product field to Value area. By default, it will show Count. Right Click in the Pivot Table and choose Summarize Values By – More Options …
In this dialog, scroll the list – at the bottom you will see Distinct Count. That’s it.
Another method is available. Go to Data Model. Click inside the Product column. From the Home tab – under Calculations group open the AutoSum drop down and choose Distinct Count. Save.
Now in the Power Pivot Table field list, you will see distinct count as a separate field.
Alternatively, you could also have added this is a new measure or calculated field with the formula:
=DISTINCTCOUNT([Product])
Another simpler method
Click inside a table. Choose Insert tab – Pivot Table. The table name will be automatically added as the data source for the pivot table.
At the bottom, enable the checkbox Add this data to the Data Model.
The pivot table which gets created now has the same feature in Value Field Settings as shown above.
How to use Distinct Count
As this is a new concept for most of us, the simplest way is to use Distinct Count in addition to a traditional Count feature. Comparing count and distinct counts will definitely provide you with new and useful information abut the same data.
Try it out NOW!
Want to know more about Power Pivot? Refer to the Data Analytics Knowledge Pack.
***
