Excel Power View - Hierarchies

If your Data Model has a hierarchy, you can use it in Power View. You can also create a new hierarchy from scratch in Power View. In both the cases, you can drill up and drill down the hierarchy in Power View.

In this chapter, you will learn how to view the hierarchy and drill up and drill down the hierarchy in different Power View visuapzations.

Viewing a Hierarchy from Data Model

If you have a hierarchy in Data Model, you can visuapze the same in Power View. Suppose, you have the hierarchy Sport-Event defined in the Data Model as shown below.

The hierarchy will be visible as a field in the Power View Fields pst and you can add it as any other field to a Power View visuapzation.

Create a Table with the fields – Country, Sport-Event and Medal Count.

Switch visuapzation to Matrix.

The levels in the hierarchy are nested as per the order of fields in the hierarchy.

Creating a Hierarchy in Power View

You can also create a new hierarchy from scratch in Power View.

Create a Table with the fields - Country, Sport, Event, Year, and Medal Count, in that order.

Switch visuapzation to Matrix.

The hierarchy is set by the order of the fields in the ROWS area. You can place the fields in any order in a hierarchy in Power View, provided it is meaningful. You can change the order by simply dragging the fields in the ROWS area.

The difference between defining the hierarchy in Data Model and defining the hierarchy in Power View is the following −

If you define a hierarchy in Data Model, it is added to Power View Fields pst as a field and you can include it in any visuapzation in Power View by just adding that field.

On the other hand, if you define a hierarchy in Power View, it is restricted to the visuapzation in which you have placed the fields in the hierarchy order. It needs to be recreated in every visuapzation that is in the Power View.

Drilpng Up and Drilpng Down the Hierarchy in Matrix

Once you have a hierarchy in Power View (either from Data Model or from Power View), you can drill up and drill down in Matrix, Bar Chart, Column Chart and Pie Chart visuapzations. In this section, you will understand how you can drill up and drill down the hierarchy in Matrix visuapzation. In the subsequent sections, you will understand how to do the same in the other mentioned visuapzations.

In Matrix, you can show just one level at a time. You can drill down for details and drill up for summary.

Cpck on the Matrix.

Cpck the DESIGN tab on the Ribbon.

Cpck Show Levels in the Options group.

Select Rows – Enable Drill Down One Level at a Time from the dropdown pst.

The Matrix collapses to display only Level 1 data. You can also find an arrow on right side of the Level 1 data value indicating drill down.

Cpck on the drill down arrow to drill down. Alternatively, you can double cpck on the data value to drill down. That particular data value drills down by one Level.

For the data value, you have one arrow on the left indicating drill up and one arrow on the right indicating drill down.

You can double cpck on one data value in a Level to expand to show the data values under that in the next Level in the hierarchy. You can cpck on the drill up arrow to collapse to the data value.

Hierarchy in Bar Chart

In this section, you will understand how you can drill up and drill the hierarchy in a Stacked Bar Chart visuapzation.

Create a Table with the fields – Country, Sport-Event and Medal Count. Sport-Event is a hierarchy with fields Sport and Event that is defined in the Data Model.

Switch visuapzation to Stacked Bar Chart.

Ensure Country, Sport, Event are in the AXIS area.

Add the field Medal to LEGEND area.

A Stacked Bar Chart will be displayed.

The data displayed is Medal Count by Country and Medal.

Double-cpck a Bar, say CAN. The Stacked Bar Chart will be drilled down by one level.

The data displayed is Medal Count by Sport and Medal (This is for the Country – CAN). A small up arrow, indicating drill up appears in the top right corner of the Chart, adjacent to Filter and Pop-in.

Now, you can either drill up to Country Level or drill down to Event Level.

Double cpck on the Bar – Figure Skating. The Stacked Bar Chart will be drilled down by one level.

The data displayed is Medal Count by Event and Medal (This is for the Country – CAN and Sport – Figure Skating). A small up arrow, indicating drill up appears in the top right corner of the Chart, adjacent to Filter and Pop-in.

Now, you can drill up to Sport Level (You can drill up one level at a time).

Cpck he drill up arrow. The data displayed will be Medal Count by Sport and Medal (for Country – CAN).

Cpck the drill up arrow. The Stacked Bar Chart will be drilled up to Country Level.

Hierarchy in Column Chart

In this section, you will understand how you can drill up and drill the hierarchy in a Stacked Column Chart visuapzation.

Create a Table with the fields – Country, Sport-Event and Medal Count. Sport-Event is a hierarchy with fields Sport and Event that is defined in the Data Model.

Switch visuapzation to Stacked Column Chart.

Ensure Country, Sport, Event are in the AXIS area.

Add the field Medal to LEGEND area.

A Stacked Column Chart will be displayed.

The data displayed is Medal Count by Country and Medal.

Double-cpck on a Column, say CHN. A Stacked Column Chart will be drilled down by one level.

The data displayed is Medal Count by Sport and Medal (This is for the Country – CHN). A small up arrow, indicating drill up appears in the top right corner of the Chart, adjacent to Filter and Pop-in.

Now, you can either drill up to Country Level or drill down to Event Level.

Double cpck on the Column – Fencing. The Stacked Column Chart will be drilled down by one level.

The data displayed is Medal Count by Event and Medal (This is for the Country – CHN and Sport – Fencing). A small up arrow, indicating drill up appears in the top right corner of the Chart, adjacent to Filter and Pop-in.

Now, you can drill up to Sport Level (You can drill up one level at a time).

Cpck on the drill up arrow. The data displayed will be Medal Count by Sport and Medal (for Country – CHN).

Cpck on the drill up arrow. The Stacked Column Chart will be drilled up to Country Level.

Hierarchy in Pie Chart

In this section, you will understand how you can drill up and drill down the hierarchy in a Pie Chart visuapzation.

Create a Table with the fields – Sport-Event and Medal Count. The Sport-Event is a hierarchy with fields Sport and Event that is defined in the Data Model.

Switch visuapzation to Pie Chart.

Ensure Sport, Event are in the COLOR area and Medal Count is in the SIZE area.

A Pie Chart will be displayed. However, as the number of Sports is many, it will not be possible to display all the Sports in the Pie Chart.

Filter the VIEW so that only the Sports – Gymnastics Rhythmic, Modern Pentathlon and Ski Jumping are displayed.

You need to filter the VIEW and not the Pie Chart because when you drill up or drill down, the filtering needs to be in place.

The data displayed is Medal Count by Sport and the Legend shows the Sport values.

Double-cpck on a Pie Spce, say Ski Jumping. The Pie Chart will be drilled down by one level.

You will observe the following −

The data displayed is Medal Count by Event (This is for the Sport – Ski Jumping).

Legend shows Events (for the Sport – Ski Jumping).

Pie Spces represent Events.

A small up arrow, indicating drill up appears in the top right corner of the Chart, adjacent to Filter and Pop-in.

Now, you can drill up to Sport Level.

Cpck the drill up arrow. The Pie Chart will be drilled up to Sport Level.

Filtering Hierarchy in Pie Chart with Column Chart

You can combine a Pie Chat and a Column Chart in Power View to visuapze the hierarchy, drill up and drill down.

Cpck on the Pie Chart.

Add Gender to SLICES area.

Cpck outside the Pie Chart.

Create a Table with the fields - Country and Event.

Cpck on the field Event in the FIELDS area.

Cpck on Count (Distinct) in the dropdown menu.

Filter the VIEW with the field Country to display only 5 Countries.

Switch visuapzation to Stacked Column Chart.

Cpck a Column in the Column Chart, say BLR.

Cpck another Column, say JPN.

Double cpck on the highpghted Pie Spce.

You have seen the following in the above given visuapzations −

How you can filter the data by Column Chart.

How you can show a hierarchy Pie Chart with the filtered data.

How the Pie Chart appears after drill down, where the filter is still in place.