Hierarchies in power pivot

One way you can modify a PowerPivot Data Model is to add a hierarchy. For example, if you have geographic data, you can add a hierarchy that has country at the top, and drills down to region, state, and city.

A hierarchy is a list of columns that roll up to a single item 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, you'll need to enable the Power Pivot add-in.

Follow these steps:

1. Open the Power Pivot window.

2. Click Home > View > Diagram View.

3. 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 columns you've chosen.

5. Click Create Hierarchy to create a parent hierarchy level at the bottom of the table. The columns will copy into the hierarchy as child levels.

6. Enter a name for your new hierarchy.

7. Drag more columns into the hierarchy parent level, which creates child levels from the columns and places the levels at the bottom of the hierarchy.

   Optionally, you can place a column into a particular location of the hierarchy list by dragging a column to place the child level precisely 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 set according to the cardinality of the columns. The highest cardinality appears first in the list, in which values are the most uncommon and unique—such as identification numbers and names. The columns with the lowest cardinality appear last in the list—in which values are more common, such as status, Boolean values, or common classifications. Keep in mind that 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 to create as child levels in your hierarchy, click the Create Hierarchy item in the context menu 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 only add a column once to a single hierarchy. After you add a column to a hierarchy, you cannot add it to the same hierarchy again. This means that you won't 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

There are several options to accomplish this:

• Drag a column onto the parent level of the hierarchy to copy the column as a child level at the bottom of the hierarchy list.

• Or, drag a column into a specific location in the hierarchy to copy the column as a child level of the hierarchy.

• Or, right-click a column—or multiple selected columns—then click Add to Hierarchy in the context menu, and then click the specific hierarchy. This creates a child level from the column and adds it 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 the Delete key. If you want to reclaim this child level, simply 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.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.