Pivot Table - Drill Down does not filter rows

[BrianGGG]

I can't tell whether this is the expected behavior or not, but I am getting more data than I would have expected when I do "show details" or Drill Down from within a Pivot Table.

Here is a simple dataset:

	A	B	C
Date	Val 1	Val 2

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

</tbody>

Test​

Here is a pivot table of that data with two values:

	I	J	K
Row Labels	Sum of Val 1	Count of Val 2
01-Jan
02-Jan
Grand Total

<tbody>
[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]

</tbody>

Test​

If I double click on the "3" value for January 1, here's where I get too much data:

	A	B	C
Date	Val 1	Val 2

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01/01/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01/01/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01/01/19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]01/01/19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01/01/19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

Shouldn't this drill down just return the three rows that make up the "3" value in the table rather than all 5 values for Jan 1?




Thanks
BrianGGG

 

[James006]

Hi,

May be an issue related to slicers ...

Take a look at Debra's explanation :

https://www.contextures.com/excel-pivot-table-drilldown.html

Hope this will help

 

[BrianGGG]

Thanks James006.

Unfortunately, I don't think my situation is related to the slicers problem described in the article. I have Excel 2016, and according to the article this problem is fixed in this version.
Also, in the Show Details example, the drill down only shows the selected criteria rather than the entire set of rows (in the article example it's 2014 and East in both the pivot and the drill-down.

 

[Fazza]

Hello, Brian

Thank you for presenting the data so well.

From a quick reading, the results you're getting are expected & correct.
Drilling down on the "3" result for 01/01/19 returns entire records (that is all fields) for all records that are 01-Jan. There is no other filter - so all records for 01/01/19 are returned. This is what all versions of Excel I've used do.
There are five records returned because there are five records for 01-Jan.

If you only wanted the three records that have a particular value (of 1, say), then the "Val 1" field would need to be there as either a row or column (or even page) field. This provides the filtering.
So if the 'Val 1" field was also a row field, say at the level below the dates, then there would be data of Sum of "Val 1" for 01-Jan for each value of "Val 1"
That would be row "0" with a sum of 0, and then row "1" with sum of 3.
Similarly if column field "Val 1", there would be new columns with value "0" & its sum 0, and value "1" with its sum 3.
In these cases, drilling down on the sum 0 would return the entire two records of value 0, and, drilling down on the sum 1 would return the entire three records with individual values of 1.

OK?

 

[BrianGGG]

Thanks to you also for presenting the answer very clearly.
I get it now.

For measures (count, sum), they do not play into the output of drill down.
If I put one of the values in a column and then sort by the "1" value, the drill down will only show the records with the "1" value.


Much appreciated...


Regards,
Brian

 

[Fazza]

You're welcome, Brian. All the best, Fazza