The following is a screenshot of a pivot table I have created in Excel.
The odd thing is that the column total of the distinct count is being displayed as 71 but if you highlight the values (or add them manually) then the total comes to 90 (as you can see in the corner of Excel).
When I generated the pivot table, I select the "Add this data to the model" option, so that I can use the Count Unique option.
Does anyone have any idea why the two values are different?
The reason becomes obvious from the example in the picture.
You group by the Name column containing the values {'A','B','C'} and for each of the values there is an entry of {'A','B','C'} in the Char column.
No you can see, that for each element there are 3 distinct values in the column Char, however, in total there are also only 3 distinct values.
The grand total can here not be interpreted as the Sum of the above, but as the distinct count of the whole table.
