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 visualizations.
Viewing a Hierarchy from Data Model
If you have a hierarchy in Data Model, you can visualize 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 list and you can add it as any other field to a Power View visualization.
- Create a Table with the fields – Country, Sport-Event and Medal Count.
- Switch visualization 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 visualization 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 list as a field and you can include it in any visualization 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 visualization in which you have placed the fields in the hierarchy order. It needs to be recreated in every visualization that is in the Power View.
Drilling Up and Drilling 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 visualizations. In this section, you will understand how you can drill up and drill down the hierarchy in Matrix visualization. In the subsequent sections, you will understand how to do the same in the other mentioned visualizations.
In Matrix, you can show just one level at a time. You can drill down for details and drill up for summary.
Select Rows – Enable Drill Down One Level at a Time from the dropdown list.
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.
Click on the drill down arrow to drill down. Alternatively, you can double click 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 click 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 click 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 visualization.
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 visualization 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-click 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 click 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).
Hierarchy in Column Chart
In this section, you will understand how you can drill up and drill the hierarchy in a Stacked Column Chart visualization.
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 visualization 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-click 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 click 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).
Hierarchy in Pie Chart
In this section, you will understand how you can drill up and drill down the hierarchy in a Pie Chart visualization.
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 visualization 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.
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-click on a Pie Slice, 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 Slices 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.
Filtering Hierarchy in Pie Chart with Column Chart
You can combine a Pie Chat and a Column Chart in Power View to visualize the hierarchy, drill up and drill down.
- Click on the Pie Chart.
- Add Gender to SLICES area.
- Click outside the Pie Chart.
- Create a Table with the fields - Country and Event.
- Click on the field Event in the FIELDS area.
- Click on Count (Distinct) in the dropdown menu.
- Filter the VIEW with the field Country to display only 5 Countries.
- Switch visualization to Stacked Column Chart.
Click a Column in the Column Chart, say BLR.
Click another Column, say JPN.
Double click on the highlighted Pie Slice.
You have seen the following in the above given visualizations −
- 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.
