Search code examples
excelpivot-tablepowerpivot

Cannot group fields in Pivot Tables

Software: MS Excel 2016

Excel File: GitHub Repository

I am unable to group fields in pivot table of Excel file.

Graffiti table is linked with Calendar table via Date (in PowerPivot > Data Model), so why is there difficulty in grouping the following.

However if I unlink the tables (which is what I DO NOT want to do), then it works

enter image description here

Solution

  • In his online course, Building BI with Pivot Tables, Ken Puls, Microsoft Excel MVP explains that if you build your pivot table from the data model, grouping won't work. He also explains that the only exception to this is that for Excel 2016 they fixed the ability to group dates. He says other grouping still doesn't work in Excel 2016. If you make your Pivot Table from your "Table1" in your "DSNY_Graffiti_Information" tab in your Workbook instead of from the Power Pivot data model, you can group:

    enter image description here

    For the Pivot Table above, I just clicked in "Table1" in your "DSNY_Graffiti_Information" tab, then used Insert -> Pivot Table, and I used your "RESOLUTION_ACTION" column for both the "Rows" and "Values". It defaulted the values to count (i.e., "Count of RESOLUTION_ACTION").

    enter image description here

    Then I selected all of the "10-..." entries in the Pivot Table, right-clicked, and clicked "Group".

    I hope this helps some.