Distinct Count in Pivot Tables with the Data Model

February 14, 2018 - by Bill Jelen

Today's tip is from Excel project manager Ash Sharma. I asked Ash for his favorite Excel tips and will feature them for the next seven Wednesdays. His first tip involves a really simple way to calculate a distinct count in an Excel pivot table. It all comes down to one simple check box.

Consider the pivot table shown here. Sector and Customer are on the Rows area of the pivot table. You can visually count that two customers appear in the Applications sector and four customers appear in the Consulting sector.

But here is the problem. Drag the Customer field from Rows to Values. The calculation automatically changes to a Count. The field says Count of Customer, but it is not a unique list of customers. The 109 in the figure below means that the two customers in the Applications sector had 109 transactions.

Here is Ash's solution. Completely start over. Delete the original pivot table. Select one cell in your data. Insert, Pivot Table. In the Create PivotTable dialog box, choose the setting for Add This Data To The Data Model.

Build the pivot table by dragging Sector to Rows and Customer to Values. Initially, the pivot table gives the wrong information as in the first example. Double-click the Count of Customer heading to open the Field Settings dialog. Scroll all the way to the bottom and choose Distinct Count

The result: your pivot table now shows a distinct count.

Thanks to Ash Sharma for listing this as one of his favorite tips. Watch for another tip from Ash next Wednesday.

I love to ask the Excel team for their favorite features. Each Wednesday, I will share one of their answers.

Title Photo: Simone Acquaroli / Unsplash