Use hierarchies in PivotTables

Recall from my previous post Create a hierarchy in PowerPivot Data Model, We will continue from where we left our earlier post.

Now we have a Sports hierarchy and Locations hierarchy, we can add them to PivotTables or Power View, and quickly get results that include useful groupings of data.

Prior to creating hierarchies, you had to add individual fields to the PivotTable, and arrange those fields how you wanted them to be viewed.

In this post we will use the hierarchies created in the previous post to quickly refine our PivotTable.

Open the Excel workbook which we used in our previous post and Insert PivotTable as shown in below screen.

• Add field in the PivotTable Medal from Medal Table in the FILTERS area, and Count of Medal from Medal Table in the VALUES area. Your nearly empty PivotTable should look like the following screen.

• From the PivotTable Fields area, drag SDE from the Events table to the ROWS area. Then drag Locations from the Hosts table into the COLUMNS area. Just by dragging those two hierarchies, your PivotTable is populated with a data, all of which is arranged in the hierarchy we defined in the previous steps. Your screen should look like the following screen.

• You can expand any of those Sports in the PivotTable, which is the top level of the SDE hierarchy, and see information in the next level down in the hierarchy (discipline). If a lower level in the hierarchy exists for that discipline, you can expand the discipline to see its events. You can do the same for the Location hierarchy, the top level of which is Season, which shows up as Summer and Winter in the PivotTable.

By dragging those two hierarchies, you quickly created a PivotTable with interesting and structured data that you can drill into, filter, and arrange.

I will come up with more details and other options on this topic in my upcoming posts. Till then keep learning and practicing.