Can't apply value filter to excel pivot table without affecting subtotals - Subtotal filtered items greyed out

Question

I have a pivot table containing a list of assemblies and the costs of the parts, with the subtotals equating the total cost of the assembly. I want to filter out the parts of negligible cost (say <.50$ or so), but everytime I apply a value filter my subtotal adjusts accordingly as well.

The solution given by excel is to go to options - options tab for the pivot table, then the "Totals and Filters" tab, then check "Subtotal filtered items" box. The problem is that box is greyed out on me. Quick google search said to make sure the spreadsheet is saved as a 2007 version spreadsheet... tried that to no avail. Wondering if anyone else has ever had a similar issue and found a solution.

Answer 1

I would go back to the source table and add a calculated column e.g. "Is Significant", with a forumla like this:

=IF([@amount]<=0.5,"Negligible","Significant")

Then I would return to the Pivot Table and refresh it to see the new "Is Significant" field. Then I would drag it into the Pivot Table, e.g. if the "Assembly Name" is already in the Row Labels, then I would insert new "Is Significant" field above "Assembly Name".

Now the Pivot Table rows are grouped by "Is Significant". Finally I would sort the "Is Significant field in the Pivot Table in Z-A order, and collapse the "Negligible" node to hide those rows.

Here's a screen shot of a simple example: