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.
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.
