Search code examples
excelpivot-table

Excel pivot table "Distinct Count" not compatible with Group for dates?

Using Excel 2013, I need a pivot table which:

  • Counts unique values in a field ('Distinct Count')
  • Groups the data by Year and Quarter

If I create the pivot table by checking "Add this data to the Data Model" then the "Distinct Count" option is available as expected. However, I cannot group my date columns (both Group and Ungroup are grayed out), making the report unusable.

OTOH if I don't check "Add this data to the Data Model" when I create the pivot table, Grouping dates works fine but I lose the "Distinct Count" option as expected. Exact same data with date column formatted as short date.

Can someone verify and/or enlighten me about the reason?

Solution

  • Correct: the grouping feature is not available in Pivots based on the Data Model, and the Distinct Count feature is not available on 'traditional' pivots. But you can either use formulas on your source data to add the groups there, and pivot that field, or use DAX formulas to create groups within the Data Model.