Forum Discussion
Problem with Drill-down pivot table
Hi
I have three pivot tables with several slicers connected to each of the tables.
When I drill down to details in anyone of the tables it doesn't always reflect the filters from the slicers.
It seems that if one single value is filtered from one slicer, the details still include all the data from that slicers values. If two or more values is filtered in the slicer, the drill down seems to include the correct data.
The discussed work-around of adding an actual filter to the pivot tables doesn't solve the problem, also that bug should be solved in Excel 2016, which is the version I use.
The pivot tables are based on data from PowerPivot (excel table)
Any suggestions on how to address this?
Best regards
Fredrik
28 Replies
Hello Fredrik,
have you found a solution yet?
I am facing the same problem.
I've created a dashboard with many data slicers and an option to drill down the filtered data.
If there is a data slicer with multiple selections and any other data slicers with at least one filter, then the drill down ("show details" / data extract) won't show the correct number of entries. Some data slicers will be just ignored. It's very confusing.
This only happens when the data comes from the power pivot data model. I've created the same dashboard having the complete data in a work sheet. There it works without any problems. I only get the filtered data back when I drill down, no matter how I set the data slicers.
This seems to be a bug to me. In Excel 2013 it wasn't possible to select multiple values within a data slicer and afterwards to drill down. Finally it works in Excel 2016, but it's a really weird bug, that it doesn't work correctly when the data comes from the Power Pivot data model.
Best regards,
Virty
Hi Virty
No, I gave up and created the same dashboard with pivottables and slicers from a regular Excel table instead. For my needs it wasn´t really necessary with a Powerpivot table I only wanted the option to create measures in an easier way.
It really looks a bug to me too, at least I haven´t found any good reason why this functionality would differ Power Pivot models/regular excel tables. Because one thing is clear, it does differ.
Best regards
Fredrik
Hello Fredrik,
thanks for your fast reply.
I am also sure, that this must be a bug.
It's a pity that I have to create my dashboard based on a regular worksheet now. It increases the file size by several times and also my measures don't work anymore, so I have to use some work-arounds based on normal pivot tables.
Best regards,
Virty
- This is "by design". If you have a slicer tied to a field that isn't used anywhere in the pivottable, the slicer filter is ignored during drill-down. Add the slicer's field to the page filter area and it should work as expected.
Hello
Yeah but that's the thing, it doesn´t make any difference having the slicer fields as page filters.
Attach an example of the workbook for clarification, see sheets Case 1a & b. Produce the same results with different filters.
Maybe it has something to do with my created measures in Powerpivot but can't figure out what could be wrong there
BR
Fredrik
- Odd. If I filter for 40, I do get the expected 7 records, filtering for 42 yields 13 records and filtering for 40 and 42 gives precisely 20.
