Problem with Drill-down pivot table

[Fredrik Nilsson]

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

[#2]

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.

[#3]

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

[#4]

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.

[#5]

Ok good someone's getting it right :)

That's really hard to explain.

[#6]

I've really been struggling with this for days now. Selecting single values from slicers works fine (besides the result will include records for both measures even if I only drill on one of them). Selecting one value from one slicer AND multiple values from another slicer give an incorrect result. Multiple values from multiple slicers also return correct result.

I thought maybe it had something to do with drilling on created measures from PowerPivot but then You wouldn't get different (correct) results. It seems it has something to do with my settings in excel or other local settings. But I can't think of what that could be?

This is really confusing.

[#7]

I strongly suspect there are some design flaws in your powerpivot model. Filtering yielding unexpected results is usually a clear indication something is wrong with the model.

[#8]

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

[#9]

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

[#10]

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

[#11]

I'm pretty sure this is not a bug but rather a design problem in your model. FIlters MUST be set on fields containing JUST the filter values. For example: Suppose your data contains a column with (possibly repeating) date values. You should never filter your pivots on that column (field). Instead, you should be adding a date table which contains all dates (NO DUPLICATES!) you have in your model, create a link between the date and the data table and then filter your model using the date field of the dates table.

[#12]

Hello Jan Karel,

I have tried what you've said and it's still not working. I still get the wrong amount of results back when I drill down having multiple selections within one or more data slicers and in one data slicer only one selection. So it's not a design problem. Seems to be a real bug.

So to make it clear:

I set select one item in any/all data slicers --> No problem.

I select 2 or more items in any/all data slicers --> No problem.

I select 2 or more items in any data slicers, but in one data slicer I select only 1 item --> Wrong amount of data sets in the drill down!

Best regards,

Virty

[#13]

I would need your workbook to try and figure out what the problem is.

[#14]

Hello Jan Karel,

please find attached an example file.

I've created two sheets (once the data is stored in Power Pivot and the other one catches the data directly from the work sheet) and I've set already the filters in the data slicer. Make a drill down and you will see, that you will get two different results. The one from PowerPivot will give you the wrong number of results.

Best regards,

Virty

[#15]

Hello Jan Karel,

please find attached an example. I've created two worksheets. One uses PowerPivot, the other one gets the data directly from the worksheet. I've also already set the filters. Try a drill down in both sheets. You will see different results. The one from the data model in PowerPivot will return a wrong results.

Best regards,

Virty

[#16]

Hello Jan Karel,

I cannot add an attachment. It's always marked as spam. Very strange.

So here is a link to an example file:

Google Drive Link to Example File

I've created two worksheets. One uses PowerPivot, the other one gets the data directly from the worksheet. I've also already set the filters. Try a drill down in both sheets. You will see different results. The one from the data model in PowerPivot will return a wrong result.

Best regards,

Virty

[#17]

Hello Jan Karel,

how can I attach files or send a link here in this discussion? My posts always get deleted or blocked automatically, when I try to add a document or a link to my post.

[#18]

Sorry about that. Our system has an automatic spam filter. Every weekday, our team manually goes through the spam quarantine and moves messages out of it that don't belong. I've unmarked your posts as spam.

Feel free to delete any extraneous/repeat posts as I wasn't sure which was the one that best represented what you wanted to say. 

[#19]

Now I understand. This is a known issue with pivot tables in Excel: If a slicer filters your data busing a field which is NOT in the pivottable anywhere, the filter on that field is not used in the drill-down. The work-around is to add the slicer field to the pivot table, for example as a page filter.

[#20]

Hello Jan Karel,

this workaround won't help. I've added all slicers to the page filter. The result of the drill down is still wrong, when the data source is coming from Power Pivot.