Pivot Table - Drill Down does not filter rows

BrianGGG

Board Regular
Joined
Mar 5, 2016
Messages
61
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:

ABC
1DateVal 1Val 2
201-Jan1
301-Jan1
401-Jan01
501-Jan01
601-Jan1
702-Jan01
802-Jan1
902-Jan0
1002-Jan1

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Test





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

IJK
7Row LabelsSum of Val 1Count of Val 2
801-Jan32
902-Jan21
10Grand Total53

<tbody>
</tbody>
Test





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


ABC
1DateVal 1Val 2
201/01/191
301/01/191
401/01/1901
501/01/1901
601/01/191

<tbody>
</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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

BrianGGG

Board Regular
Joined
Mar 5, 2016
Messages
61
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

Board Regular
Joined
Mar 5, 2016
Messages
61
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,682
Messages
5,524,257
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top