I usually don’t write a lot of posts asking for favours from my readers (in fact, this is the first one), but an incident happened today at my client site which has aggravated a pet peeve that I have with Power View – drill-down behaviour in Power View. The drill-down behaviour in Power View is really unintuitive and this is at least the the third client of mine that has raised this issue of drill-down. That is when I decided to raise my voice and ask for your help too in letting Microsoft know about this issue, so that they can consider changing this in a future release. For those of you who don’t have the time to read this post of mine, I would really appreciate if you could vote this Connect issue that Leonard Murphy has raised. For those of you who have the time, follow my post below to see the current drill-down behaviour in Power View and why I feel that this is not right.
For this demonstration, I am using Power View in Excel 2013 (although this behaviour can be reproduced in Power View for SharePoint also) and a very simple table (which shows the sales by countries and states) as my source.
I) How to Implement Drill-Down in Matrix
1) Open Power View and then select the three columns to make a table.
2) Click on the Table icon and select the Matrix option. Now you can see that the table has been converted into a matrix.
3) Now select the Show Levels icon and click on Rows – Enable Drill Down One Level at a Time.
4) You can see that we have achieved the drill down functionality in the matrix. Click on US and see the results.
Also note the drill down and drill up icons.
II) How to Implement Drill-Down in Chart
1) As in Step 1 of previous section, select all three columns to make a table. Then click on the Bar Chart option and select Stacked Bar. Ensure that the Country and State are in the Axis and Sales is on Values.
2) You can see the drill-down functionality by clicking on Canada. Also note the drill-up icon on the top right of the chart once you have drilled down.
III) Unintuitive Cross-filtering behaviour
1) Everything looks fine when you just have one chart in the canvas. Now let’s see what happens when we have 2 visualizations – lets say, a bar chart with drill-down and a table which just has State and Sales.
2) Now when you single click on a country (say India) in the chart, the table gets cross filtered for the states of the selected country.
3) Next, see what happens when you double click on a country (say India).
You can see the bar chart has drilled down into the states for the selected country as expected but look what has happened to the table. The table is now showing states for all the countries and not just the states of the selected country. This is so unintuitive as a person seeing the report will expect to see only the sales of the three states of India. This can get even more confusing if the states are not displayed on the table, and there will be no way of knowing what has been cross-filtered on the table.
4) To add to the confusion, I can press CTRL and then click on all three states. Look at the image below for the results.
You can see that the bar chart looks exactly the same as the previous image, but the table looks different. In short, the report looks exactly the same and there is no way to differentiate whether the data in the table is correct and if some cross-filter has been applied or not. What I would expect is that the drill-down always cross-filters the rest of the visualizations. Honestly, I can’t think of a scenario where I would not want the cross-filtering to happen and I would love to hear your views as comments.
IV) Call for Action – Vote up the Connect issue
If you agree with me that the cross-filtering behaviour is so non-intuitive, please vote up the connect issue. You can do so by clicking on this link, log-in and then clicking on the green up arrow.
Hopefully we will have enough votes for Microsoft to look into the issue seriously!