This problem arose while I was working in the Google Data Analytics certificate program. You can apply a filter to a pivot table in Google Sheets and then use this filter to filter out rows from the underlying table (using a Less Than Values Filter). This changes the aggregated values in the Values Field column (for the SUM, COUNT, etc. Summarized aggregates).

When I tried to do this in Excel, it would not let me filter on the underlying table rows: you can only apply this Values Filter (Less Than) to the aggregated values in the Values Field quadrant. You are able, that is, with a pivot table with customers in the Rows and customer orders in the Values quadrants, to filter on Summarized aggregates (applied to the entire customer Row) but not on the individual orders in the underlying table itself.

When I tried to apply the pivot table (Value) filter to filter rows from the underlying table, it would not give me that option. I could only apply this filter to the Summarized Values Field aggregates (SUM, etc.)

The filter in the pivot table Field List on the customer orders field (using the drop down arrow for the customer orders field) lets you select each row individually. But with an underlying table with 1M rows, you need the Values filter (Less Than) to do this, of course.

I even tried to apply the filter to the underlying table and then apply the pivot table. But the pivot table results ignored the filter. If I tried to filter out the customer orders of a given value, the Summarized aggregates still only applied to the entire customer’s orders.