Skip to content

PivotTables · Medium

How do you get a distinct count in a PivotTable?

Short answer

Tick 'Add this data to the Data Model' when creating the PivotTable; Value Field Settings then offers Distinct Count. Alternatively, in Microsoft 365, use =COUNTA(UNIQUE(FILTER(...))) with a formula outside the pivot.

Detailed answer

Formula alternative:

=COUNTA(UNIQUE(FILTER(Sales[customer_id], Sales[region]="South")))

Distinct counts are common in interviews ("how many unique customers bought in each region?") because a plain count double-counts repeat buyers.