Drill Down in Pivot Chart
Charts and Visualization Power Pivot

Drill Down/Drill Up Feature in Pivot Chart

In this article, we have created a drill down/drill up functionality in a Pivot chart. We have created a hierarchy in Power Pivot data model to create this.

Below are the steps to create the hierarchy in Power Pivot-  

  • Open the Power Pivot window.
  • 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 does not include all of the columns you want to use, you can add them using RELATED function of DAX.
  • Right-click one of the columns you’ve chosen.
  • 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.
  • 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.

 

Power Pivot Data Model
Power Pivot Data Model

 

Now if you will use this hierarchy in a Pivot chart or Pivot Table then drill up/drill down option will be available on right click on any slice of the chart. You can double click on any slice of the chart to drill it down to the next level.

Click here to download the practice file.

Watch the step by step video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com