Pivot Chart with Hierarchy Field
You love what you see?
What you see in the clip is a demonstration of how you can drill down a chart and analyze the details underneath the category or the group. This is a very helpful feature in Excel and you can quickly navigate to the data you would like to analyze. With this, you can actually save up tons of time in creating so many different charts just to analyze the data in different group dimension. You’re in a way turning your chart interactable.
In order to achieve this, you will first have to create hierarchy in Power Pivot (Check out the previous part) or Power View. With that you will be able create this type of drill up/ down report.
Creating Drill down/ up Pivot Chart
If you have been following me, in the previous part, I talked about how you can create hierarchy using Calendar table in Power Pivot. From there, we created a Pivot Table. Now, your Excel sheet should have already had a Pivot Chart that built using hierarchy field as similar in the figure shown below:
To insert a Pivot Chart, make sure your cursor is focusing anywhere within the Pivot Report you’ve just created, insert any chart that you wold like to use to visualize from the “Insert” Tab.
After you have inserted the chart, try this. Double click at the column that you would like to analyze, you should be able to achieve the result as similar in the clip. To drill up, select the drill up button from the Pivot Chart Tools: Analyze Tab
Having fun eh? No, the party has not even started. Do you realize there is a little issue here? You eventually get confuse while drill up/ down the report. What I realize is, while you drilled down the chart into a certain level, you have no idea which fiscal year or fiscal quarter you’re in until you drill yourself back up to the highest level. Now you see the problem here? So, for my understanding, there is yet a direct method for the chart of Pivot Report to display which category. The only solution is to work around with the tools in pivot table.
Adding slicer
So, my method of making it works is use slicer to display what are the category that is being drilled down. Why slicer? One thing I love about slicer is, it has a setting of allowing user to hide items that currently has no data in the Pivot Table or Pivot Chart.
If you’re adding slicer into the worksheet, add the slicer field that are not from the hierarchy, but outside the hierarchy.
Check out the figure below to see which are the field that are not from the hierarchy field:
So, I added the slicer of “FY”, “Quarter” and “Month” into the worksheet. Arrange and align the slicer at one side. The next step you might find it totally unnecessary, but for me, I like to have a very clean view in my worksheet while I’m analyzing it.
Hide Items with NO Data
Select your slicer object and reach out to the Slicer Tools: “Option” Tab, look for the button “Slicer Settings”. Once you are being prompt the Slicer Setting Dialog Box, check the checkbox “Hide Item with no Data” and select “OK”.
Do these to all the other slicer that you have just inserted. Now begins the drill down again. Every time when you a report is drilled down into a particular Fiscal Year, check out the items in the slicer. All the irrelevant items will be hidden during the drill down.
As I mentioned, you can totally skip this part of not checking the checkbox for “Hide item with no data”. If the checkbox is left unchecked, you will see the items has no data will then turn a little transparent just like as what shown in the clip below:
Adding Dill down/ up button to quick access toolbar
You can totally skip this step as well, but if you do just that, it is easier for you to locate the drill up/ down button. If you have no idea what quick access toolbar (A.K.A. QAT) is, now you will know. One thing about Microsoft is they allow you to customize the interface of your workplace especially when you have that few favorite buttons of yours, you can add them to QAT. Where?
Once you have added the button to QAT, you can now reach out to the button no matter what tab is current active. Right click the “Drill up” button and select “Add to Quick Access Toolbar” and voala! is that simple. Do the same to the “Drill Down” button as well. To remove, right click the button from QAT and select “Remove from Quick Access Toolbar” and simple as that!
