You can add hierarchies to a Power Pivot Data Model. For example, if you have geographic data, you can add a hierarchy that has country or region at the top, and drills down to state, and city.
A hierarchy is a list of columns that roll up to a single item in a PivotTable 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 PivotTables. To create hierarchies, first open the Power Pivot window. If the Power Pivot tab isn't available, you may need to enable the Power Pivot add-in.
To open Diagram View
Follow these steps:
Open the Power Pivot tab.
Select Manage.
Click Home > View > Diagram View.
In Diagram View, select one or more columns in the same table that you want to place in a hierarchy. If the table doesn't include all of the columns you want to use, add them by using RELATED. See RELATED Function (DAX).
Right-click one of the columns you selected.
Click Create Hierarchy to create a parent hierarchy level at the bottom of the table. The columns copy into the hierarchy as child levels.
Enter a name for your new hierarchy.
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.Note
- 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 more 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 extra 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 select 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 select Move Up to move the level up in the list. Or, select Move Down 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 can't add it to the same hierarchy again. This condition 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 no longer references the hierarchies to which the column is already added. If there are no other hierarchies to which a column can be added, the Add to Hierarchy option doesn't appear in the menu.
To add another child level to a hierarchy
Use one of the following options:
- 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.
- Drag a column into a specific location in the hierarchy to copy the column as a child level of the hierarchy.
- Right-click a column or multiple selected columns, select Add to Hierarchy in the context menu, and then select the specific hierarchy. This action 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 select Remove from Hierarchy. Or, select 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 you created it 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 select Hide Source Column Name or Show Source Column Name to toggle between the two options.
When you select 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 or get support in Communities.