excelcountpivot-tabledistinct-values

Excel Distinct Counts on Pivot Tables are aggregating incorrectly

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

table I am using for data

Distinct count filters I am using

Below is the output, that the pivot table is currently providing as you can see, DISTINCT count for aide should be 1 in match and science, there is only 1 student but the distinct count is 2

I would like the ideal output to look like this:

Desired output

Solution

  • 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.