Excel Distinct Counts on Pivot Tables are aggregating incorrectly - Stack Overflow

I have the followng table in Excel and I am trying to aggregate the distinct counts, in a pivot table, however the values are off and I am wondering why. I am using the distinct count option within the pivot values for the student,teacher and aide

Below is the output, that the pivot table is currently providing

I would like the ideal output to look like this:

I have the followng table in Excel and I am trying to aggregate the distinct counts, in a pivot table, however the values are off and I am wondering why. I am using the distinct count option within the pivot values for the student,teacher and aide

Below is the output, that the pivot table is currently providing

I would like the ideal output to look like this:

• excel
• count
• pivot-table
• distinct-values

Share Improve this question Follow asked Jan 11 at 14:55 Philo Philo 5511 silver badge77 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Distinct count includes "blank", so you'll have to write your own measure field, try something like

=CALCULATE(DISTINCTCOUNT(Table1[teacher]), NOT(ISBLANK(Table1[teacher])))

Then select that field instead of Distinct Count of teacher.