Be Careful Drilling into PivotTables with Slicers

While teaching a class as the University of Delaware, Dee (one of the students) brought up the fact that PivotTable Slicers cause some strange behavior when drilling into the detail of the PivotTable. If you’ve never used slicers before, feel free to peruse my intro to Slicers.

.

I had never seen this behavior before, so I was intrigued. We spent about three minutes in the class finding a fix for it.

When I got back, a quick Google search confirmed that Debra Dalgleish of Contextures fame beat me to it and found this “quirk” earlier this year.

.

Damn It Debra! Must you be so diligent? Oh well…here is the explanation of the issue nonetheless.

.

When you create a PivotTable with a report filter (Market in this case) you can filter for a value like “Baltimore” and then drill into the details by double-clicking on any of the numbers shown. In the screenshots below, I’m double-clicking on the Grand Total to output the 19 records that make up that $19,387.

.

.

Now let’s say that instead of a Report Filter, you use a Slicer.

Doing that same double-click on the Grand Total will result in basically all the records in the Pivot Cache.

In other words, it doesn’t look like the Slicer has any affect at all on what gets output out of the Pivot Cache. You basically get all of it.

.

This is obviously not ideal if you have created a dashboarding mechanism where your clients will frequently want to drill into the details.

.

The only fix seems to be to also include the Report Filter in the PivotTable along with the Slicer for the target field.

This will negate the issue. Of course, you can hide the row that contains your report filter so your clients won’t get confused.

.

Thanks Dee for the question!

 

15 thoughts on “Be Careful Drilling into PivotTables with Slicers

  1. Debra Dalgleish

    Sorry about that! If it’s any consolation, I annoy myself too — I get a great idea for a blog post, then discover that I’ve already written about it.

  2. Rob

    Thank you for the solution on using slicers with drill down capability. I was having the problem you described above.

  3. Rachel

    Thanks for posting this. I thought something was very wrong with my pivot table. I use slicers all the time and have double clicked on other data sets that worked correctly. I just ran into this problem today. I’ll take your advice and hide the top filters so that they can focus on the slicers.

    Thanks for the fix!!!!!

  4. Amrik

    It’s nearly 2016 and I just encountered this issue – thanks for the help but this surely MS should have created a fix by now -.-

  5. JAH

    Just wanted to throw a big thank-you out for this solution. Ran into this issue today and couldn’t believe this wasn’t working correctly. I was worried it would render my slicers completely useless.

    Thanks!!

  6. Keri

    You just saved my life.
    Was asked to add some slicers to a report that clients need to drill into. Was about to loose my mind as to why the ‘drilling’ was not working.

  7. Irion

    Thank you for Pivot table report filter information !
    I intensively use slicers filtering and details digging at the same time (even if my clients don’t know it’s used because I deactivate detail to replace it by a macro that read it to put it into an userform)

    As I have 10K entries filtered, it was impossible to know why only a few of them fail the detail digging depending of slicers used.

    What’s weird with this bug is that slicers do not filter all the same data the same wrong way O_o.

  8. Carl Farrington

    FYI, I just tested this and it is fixed in Excel 2016.
    I have a 365 subscription with the choice of either 2013 or 2016 apps, and although both suites were up to date (subscription version auto updates), the problem was present in Excel 2013 but not in 2016. You would think they would fix this in a service pack for 2013. We can’t move the rest of the office to 2016 because we’re running with Exchange 2007 which won’t work with Outlook 2016.

  9. Robert Mohammed

    I am having a problem with the “timeline” component in a “cubevalue” formula. Sometimes when I select down to the “day” level I get “N/A” with message “data not available”… even though data exists for that particular date. I tried formatting my date-calendar with date and time and it temporarily fixed the issue (showing the data as expected). Then I refresh the underlying powerpivot model only to find the same issue popping up again.

    Has anyone seen the same problem?…I’m using Excel 2016.

  10. Mani

    Hi…Every time When I double click the cell(already has drill through action defined)…it opens a new sheet with all the data….but…i want to display the same sheet that was previously opened…how o fix this issue…

Leave a Reply

Your email address will not be published. Required fields are marked *