Skip to content
Related Articles
Get the best out of our app
GFG App
Open App
geeksforgeeks
Browser
Continue

Related Articles

Hierarchies in Excel Power Pivot

Improve Article
Save Article
Like Article
Improve Article
Save Article
Like Article

A Hierarchy is a system that has many levels from highest to lowest. When we have related columns in a table, then analyzing them with fixed attributes is difficult. Excel Power Pivot gives us the power to set a hierarchy to which data can be filtered and analyzed correctly according to one’s needs. In this article, we will learn how to create hierarchies in the Power pivot. 

Meaning of Hierarchy in Power Pivot 

Hierarchy in a data set helps analyze complex data easily. A hierarchy is a list of nested columns in a table. Power Pivot hierarchies provide additional features and functionalities to help classify and analyze data efficiently. Hierarchy helps provide a large amount of data in a space-efficient manner. Power pivot hierarchies have aggregate functions like Count, Min, Max, Average, and Distinct Count, which help analyze different aspects of the data. One can also move to different levels in the hierarchy by the drill up and drill down option provided by PivotTableAnalyze. For example, Department, Project, and Employee all three columns can be combined into a single column as a hierarchy. 

Creating a Hierarchy By Power Pivot

Given a data set, with a table in an excel sheet name Employee. The Attributes of the table are Employee Id, Employee Name, Department, and Salary. We need to create a hierarchy of Department ⇢ Employee Name providing Salary as values. Save the given excel sheet with employee.xlsx.

Employee-table

 

Step 1: Open a new blank sheet. Go to Power Pivot, and click on Manage

Clicking-manage-option

 

Step 2: A new window name Power Pivot for Excel is opened. 

Power-pivot-opened

 

Step 3: Go to the Home tab. Under the Get External Data section, click on From Other Sources.

Clicking-from-other-sources

 

Step 4:  A new window name Table import Wizard is opened. Under Text Files. Select Excel Files option. Click on Next

Table-import-wizard-opened

 

Step 5: Firstly, check the box, Use First Row as column headers. Then, click on Browse

Clicking-browse

 

Step 6: Select the file employee.xlsx. Click on Ok

Selecting-file

 

Step 7: The desired file is selected. Click on the Next button. 

File-selected

 

Step 8: Select the tables that you want to add to your power pivot. Click on the Finish button.

Selecting-tables

 

Step 9: The files will be imported and a success pane will appear. Click on the Close button.  

Files-imported

 

Step 10: Power Pivot window reappears. A table is inserted in your Power Pivot. 

Table-inserted-successfully

 

Step 11: Now, go to the Home tab. Under the view section, click on the Diagram View

Clicking-diagram-view

 

Step 12: The diagram view of the table appears with the names of the attributes. 

Table-and-attributes-shown

 

Step 13: Select the attributes you want in your hierarchy. For example, Department. The order in which you select the hierarchy is important, as the attribute selected first will appear at a higher level than the later selected attribute. 

Selecting-attributes

 

Step 14: To select multiple attributes, press Ctrl + mouse click

Selecting-multiple-attributes

 

Step 15: After selecting the desired attributes. Right-click on the Create Hierarchy.

Creating-heirarchy

 

Step 16: A new attribute is created. Rename the attribute to your choice. For example, renaming the new attribute to Hierarchy.

Renaming-attribute-in-hierarchy

 

Step 17: Go to the Home tab. Select PivotTable.  

Selecting-pivot-table

 

Step 18: A Create PivotTable dialogue box appears. Select the radio button, new worksheet, and click on Ok.

Creating-new-worksheet

 

Step 19: PivotTable Fields appear on the right-most side of the worksheet.  

Fields-appear

 

Step 20: Drag and Drop Hierarchy to Rows. A table with the highest level hierarchy appears. 

Highest-level-hierarchy-appears

 

Step 21: Drag and Drop Salary to Values

Salary-chosen-in-values

 

Step 22: A Hierarchy table is created. You can click on the + button, to move down to the next level in the hierarchy.  

Moving-to-next-level-of-hierarchy

 

Exploring Different Features in Power Pivot Hierarchy

Now, we will explore different features in the hierarchy offered by the power pivot. Consider the hierarchy created above. i.e. DepartmentEmployee Name

  • Changing the order in a Hierarchy

Given the Drawing View of the Employee Table. 

Drawing-view-of-employee-table

 

Use Move Up and Move Down buttons to change the order of your hierarchy. 

Step 1: Select any attribute in the hierarchy. For example, Department. Right-click on it. 

Selecting-attribute-in-hierarchy

 

Step 2: Click on the Move Down button. 

Clicking-move-down-button

 

Step 3: This moves your attribute in the hierarchy to one level down. For example, the Department attribute comes under the Employee Name now. 

Department-moving-below-employee-name

 

Step 4: Again, Right-click on the Department. Click on the Move Up button. 

Clicking-move-up-button

 

Step 5: The Move Up button moves your attribute to one level up in the hierarchy. For example, the Employee Name attribute comes under the Department now. 

Employee-name-moving-below-department

 

  • Hiding or Showing Source column name in the Hierarchy

Given the Drawing View of the Employee table. 

Drawing-view-of-employee-table

 

The Source Column Name can be different from the name in the Hierarchy. 

Step 1: The text written in the Parenthesis is the Source Column Name. By default, the Source Column Name and the Hierarchy Column Name are the same. 

Source-column-name-shown

 

Step 2: Right-Click on any attribute of the hierarchy attribute. Click on the Hide Source Column Name. 

Clicking-hide-source-column-name

 

Step 3: The text is written in the parenthesis disappears. 

Text-disappears

 

  • Drill Up and Drill Down in the Hierarchy

Consider the Hierarchical Pivot table created above. 

Hierarchal-pivot-table

 

Drill Up and Drill down are used to come down or up a level in the hierarchy. 

Step 1: Select cell B6 in the hierarchical pivot table. The department is IT.

Selecting-IT-department

 

Step 2: Go to the PivotTable Analyze tab. Click on the Drill Down button. 

Clicking-drill-down-button

 

Step 3: Now, the name of the employees will appear, working in that department. The selected cell moves down to one level. 

Employee-names-appear

 

Step 4: To Drill Up in the table. Select any cell in the Hierarchy attribute. For example, select cell B4 i.e. Arushi

Arushi-cell-selected

 

Step 5: Click on the Drill Up button. 

Clicking-drill-up-button

 

Step 6: The table reaches back to its previous level. 

Previous-level-achieved

 

Create An Hierarchy using In related Tables

Creating  Hierarchy between multiple tables is not possible in Power pivot. To add attributes of another table in your parent table can only be achieved if you add that attribute from another table to your parent table. Given a data set of two tables, one named Employee and the second named Project in two different excel sheets in the same workbook. For example, create a hierarchy of Department ⇢ Project IdEmployee Name. As Project Id is not present in the Employee table so we will add the Project Id attribute from the Project table to the Employee table.

Employee-table

 

Project-created

 

Step 1: Create a Drawing view, of both the tables in the power pivot window, by importing the file in the power pivot window. 

Importing-file-in-power-pivot-window

 

Step 2: Now, create a relation between the Employee table and the Project table. To do this, there should be at least one attribute common in both of the tables. Keep your cursor on the Employee table, select any attribute and drag the mouse to the Project table, leave your mouse click. 

Creating-relation-between-tables

 

Step 3: A small arrow line is created between the tables, showing that both tables are related. 

Arrow-line-created

 

Step 4: Go to the Home tab, and click on the Data view option. 

Clicking-data-view-option

 

Step 5: Click on the Add Column

Clicking-add-column

 

Step 6: Write a function =RELATED([Project Id]) in the ribbon. Press Enter

Related-function-added

 

Step 7: A column name Calculated Column 1 is added to the Employee table. 

Calculated-column-1-added

 

Step 8: Rename the attribute as Project Id

Renaming-attribute-as-projectID

 

Step 9: Go to the Home tab, and click on the Draw View. Select the Attributes in the hierarchical order to be created i.e. Department ⇢ Project Id ⇢ Employee Name. Click on the Create Hierarchy

Clicking-create-hierarchy

 

Step 10: A Hierarchy is created. 

Hierarchy-created

 


My Personal Notes arrow_drop_up
Last Updated : 29 Dec, 2022
Like Article
Save Article
Similar Reads