Pivot Table -- How to pass Metric as Filter when viewing Raw Data-CodePudding

Let's say I have the following data in Excel:

Book    Country Book Store  Book Is New?
Hamlet  US  Amazon  0
Hamlet  CA  Amazon  0
Hamlet  US  Barnes & Noble  0
Hamlet  CA  Barnes & Noble  0
Harry Potter 1  US  Amazon  1
Harry Potter 1  CA  Amazon  1
Harry Potter 1  US  Barnes & Noble  1
Harry Potter 1  CA  Barnes & Noble  1
Harry Potter 2  US  Amazon  1
Harry Potter 2  CA  Amazon  1
Harry Potter 2  US  Barnes & Noble  1
Harry Potter 2  CA  Barnes & Noble  1

I would like to create a Pivot Table to get the number of "New Books" per Book Store - Country combination. For example, the number of "New Books" on Amazon-US would be two -- Harry Potter 1 and Harry Potter 2. Here is the Pivot Table I have:

However, when I click into any of the metrics to get the raw data. It gives me all books at that cell-intersection. For example, if I click the Amazon-US new releases, which shows 2, it will produce 3 results, basically ignoring how the calculation is done, and passing the filters from the Rows (Amazon) and Columns (US):

Is there a way (even if it's hack-ish) to be able to pass the filter of "Book Is New?" when double-clicking a value. In other words, if it says 2, and I click it, it should show 2 data rows, instead of 3. Attached is the xlsx workbook if helpful: https://www.dropbox.com/s/uqdll138fcsfsse/SO_Example.xlsx?dl=0.

CodePudding user response：

The Show Details feature produces a subset of the source table whose entries match the current filter context for the chosen value. As you currently have it set up, both Book Store (via the Pivot Table Rows field) and Country (via the Pivot Table Columns field) make up part of that filter context. Book Is New?, however, is not applying any filter context.

In order to make it so, you could place that field in either the Filters area of the Pivot Table or else in a Slicer and set it to 1.