Excel PivotTables are great for summarizing a variety of columns of a dataset in a variety of ways. But there’s one particular use case where the classic PivotTable falls short. Let’s take a look at what that is, and offer a solution.
In this example we’d like to find the total number of distinct Order IDs and Customer IDs. Anytime I need to summarize multiple fields like this, PivotTables seem like a good idea to me. Go ahead and load the PivotTable. Check out this post if you’re not sure how.
How do classic PivotTables count?
Dragging Order ID and Customer ID into the Values section, you should see that both automatically count to 51,290:
Now I’d be very surprised if there were actually the same number of distinct customers and orders in this dataset. That is way too neat. And we can confirm this is not the case with a combination of COUNTA() and UNIQUE():
What’s the problem here? The PivotTable is simply counting the total number of records for each column. It is not counting how many of these records are distinct. You can confirm this with Order ID by placing Order ID in the Rows section and Count of Order ID in the values:
We can see that some of these orders are found multiple times in the data, and they are being counted multiple times too.
Fix this with the Data Model
While it’s difficult to get this distinct count in the classic PivotTable, we can easily get it with a so-called “Power” PivotTable. Go back to the source data and insert a new PivotTable. This time, make sure to check on Add this data to the Data Model at the bottom of the Insert PivotTable menu:
By checking on this box, you are actually passing this data to Power Pivot, which comes with tons of cool features including a slightly different PivotTable configuration.
Finding Count Distinct in the “Power” PivotTable
You’ll notice that the Fields menu of this PivotTable looks a little different, particularly in the Fields menu. Let’s find another difference that is critical to our objective: go ahead and drag Order ID to Values in the PivotTable.
The PivotTable will again count Order ID as 51,290, but this time we can fix it. Click on Count of Order ID in the Values section, then select Value Field Settings:
This menu offers several ways to summarize Order ID such as an average, min or max, and… a distinct count! You’ll find this one at the bottom of the menu. It is not available in the classic PivotTable.
You can do the same for Customer ID. Looking far more promising, right? We can do the same for Customer ID and see that there are 25,728 distinct Order IDs and 17,415 Customer IDs. These numbers look promising, as it stands to reason that individual customers place multiple orders.
It’s also a good sign that these numbers are exactly the same as what we got with COUNTA(UNIQUE()):
You can compare your workbook results to those below:
Have you used a “Power” PivotTable before? What do you think of them? Are there other shortcomings of the classic PivotTable you’ve run across? Let me know in the comments.
Leave a Reply