Show Details Pivot Table

[MarcusEstrada22]

I have a table with transactions that net out with each other sometimes, think sales and returns. I create a pivot table by another column with a transaction ID and the sum of those ID's sales or return values. If a sale is returned it uses the same ID and therefore would net to 0. When I filter out 0 values, I want to extract the remain sales that were not returned to generate a listing. However, when I "show details" of the pivot table grand total, it still produces the transactions I removed from the pivot table. Any idea on how to fix this?

If I use a value filter then I have this issue. If I filter out specific IDs, then the show details works how I want it too. However, with hundreds of thousands of rows, this isnt an option to unselect IDs.

attached is an example file

[#2]

@MarcusEstrada22 Odd indeed. Filtering out an aggregated value doesn't work the same as filtering out a label. But explaining why goes beyond my level of expertise. The reason for answering you questions is that you wrote that you are dealing with hundreds of thousands of rows. I would recommend that you look into using Power Query for this task. It's relatively easy to do this type of analyses on very large data sets and produce exactly the kind of report you need. Once set-up correctly, you can use it over and over again.

[#3]

@Riny_van_Eekelen I haven't used Power Query before, but i was able to upload it into IDEA and perform an extraction based on 3 matching columns and 1 column i added that noted which are offsetting (debit/credit for accounting). I could then rejoin the tables and exclude matches from the original that appeared in the extraction. This ended up working but wanted to avoid adding in another program and having to tailor the data again. Does power query do something similar?

[#4]

@MarcusEstrada22 Power Query is integrated in Excel as from 2016, so it's not an extra program. Am not familiar with IDEA so can't judge if or where PQ is different. What you would do in PQ is connect to your data source (a structured table, named range or sheet in another workbook (or even multiple workbooks). Then, perhaps do some very basic transformations and than Group / Sum, filter out the zeroes and load back into another sheet. Once set-up, all you need to do is press "Refresh" and a summary table will be updated. And all happens within Excel. Since you didn't mention to be working on a Mac, this should work better than using pivot tables.