24.6 Hierarchies

Hierarchies are simply hierarchical relationships in some of the fields that we have. Power Pivot allows us to define those so we can use those fields as one group as we create PivotTables. Our data related to products has three related fields: Product Category, Product Sub Category, and Product Group. These fields are ordered with Product Category being the highest level, then Product Sub Category, and then Product Group within that. For example, Accessories → Health & Fitness → Exercise Equipment is one example with Accessories being the Product Category, Health & Fitness being the Product Sub Category, and Exercise Equipment being the Product Group.

You add hierarchies in the Diagram View of Power Pivot. Find the fields you are interested in creating a hierarchy for and select them, using the Ctrl key to select multiple fields at once. Select the Product Category, Product Sub Category, and Product Category fields and then right-click and select Create Hierarchy, which will add a new group at the bottom.

You can rename the hierarchy to something more meaningful, like ProductType.

Once you have created a hierarchy, it will appear under the field list for the given table. In fact, by default, it only shows you the hierarchy, but you can still add other fields in the table by selecting the More Fields option. When the hierarchy is used, it will automatically add and nest all the fields in the hierarchy. You can selectively drill down to reveal the nested sub-categories and product groups, as shown in the image below.

Hierarchies make the data cleaner and more organized. We can drag a single field onto our PivotTable, and we get all the fields we expect.