Hierarchies in Power Pivot

One of the modifications you can make to a Data Model in the Power Pivot add-in is to add hierarchies. For example, if you have geographic data, you might want to create a hierarchy that starts with country, and drill down to region and city.

A hierarchy is a list of columns that are considered a single item when used in a Pivot or Power View report. A hierarchy appears as a single object in the Field List. Hierarchies make it easier for users to select and navigate common paths of data when creating reports and pivot tables. To create hierarchies, use the Power Pivot add-in.

1. Open the Power Pivot window.

2. Click Home > View > Diagram View.

3. While in Diagram View, select one or more columns in the same table that you want to place in a hierarchy. If the table does not include all of the columns you want to use, you can add them using RELATED. See RELATED Function (DAX).

4. Right-click one of the selected columns..

5. Click Create Hierarchy. A parent hierarchy level is created at the bottom of the table, and the selected columns are copied under the hierarchy as child levels.

6. Type a name for your new hierarchy.

7. You can then drag more columns into your hierarchy's parent level, which creates child levels from the columns and places the levels at the bottom of the hierarchy.

   Or, if you want a column in a particular location of the hierarchy list, you can drag a column to create and place the child level where you want it to appear in the hierarchy.

   Notes: 

   • When you use multi-select to create a hierarchy, the order of the child levels is initially based on the cardinality of the columns. The highest cardinality is listed first, where values are the most uncommon and unique, such as identification numbers and names, and the columns with the lowest cardinality is listed last, where values are more common, such as status, Boolean values, or common classifications. However, adding additional columns places child levels at the bottom of the list. You can drag the columns to change the order.

   • You can create a hierarchy from a hidden column (a column that is hidden from client tools).

   • If you know what columns you want created as child levels in your hierarchy, the Create Hierarchy command in the context menu enables you to multi-select those columns and quickly create a hierarchy with multiple child levels.

Edit a Hierarchy

You can rename a hierarchy, rename a child level, change the order of the child levels, add additional columns as child levels, remove a child level from a hierarchy, show the source name of a child level (the column name), and hide a child level if it has the same name as the hierarchy parent level.

To change the name of a hierarchy or child level

Right-click the hierarchy parent level or a child level, and then click Rename. Or, double-click the parent hierarchy, and then edit the name.

To change the order of a child level in a hierarchy

Drag a child level into a new position in the hierarchy. Or, right-click a child level of the hierarchy, and then click Move Up to move the level up in the list, or click MoveDown to move the level down in the list.

Note:  You can add a column only once to a single hierarchy. After you add a column to a hierarchy, you cannot add it to the same hierarchy again. As a result, you will not be able to drag a column into a hierarchy, and the Add to Hierarchy context menu for the particular column will no longer reference the hierarchies to which the column has already been added. If there are no other hierarchies to which a column can be added, the Add to Hierarchy option does not appear in the menu.

To add another child level to a hierarchy

• Drag a column onto the parent level of the hierarchy. The column is copied as a child level at the bottom of the hierarchy list.

• Or, drag a column into a specific location in the hierarchy. The column is copied as a child level of the hierarchy.

• Or, right-click a column or multiple selected columns, point to Add to Hierarchy in the context menu, and then click the specific hierarchy. A child level is created from the column and added to the bottom of the hierarchy list.

To remove a child level from a hierarchy

Right-click a child level, and then click Remove from Hierarchy. Or, click a child level in the hierarchy, and then press Delete. If you want to get the child level back, you must add the column again.

Note:  If you rename a hierarchy child level, it no longer shares the same name as the column that it is created from. By default, the source column name appears to the right of the child level. If you hide the source column name, use the Show Source Column Name command to see which column it was created from.

To hide or show a source name

Right-click a hierarchy child level, and then click Hide Source Column Name or Show Source Column Name to toggle between the two options.

When you click Show Source Column Name, the name of the column that it was copied from appears to the right of the child level.

Top of Page