filter pivot table

[cblechle]

Is there a way to filter a pivot table based on the sum values Field? What i have in the raw data is a row that contains the VIN, Make, Model, office the vehicle is located, Month, year, and the total fuel for that month. What i am doing is using a pivot table to show the months in the columns field, vehicle info in the rows, and the sum of fuel purchased for that month. What i then need to do is filter out all vehicles whos total fuel is less than 100 gallons for all the months. Is there any way to do this?

 

[Fazza]

Hi,

Some sample data would help. Maybe it can be done by just filtering the pivot table: so from the finished PT select the cell one to the right of the topmost table cell, and also the top RHS cell. Then apply a normal auto-filter ALT-D-D-F and filter as required.

Or add a new field to the source data, using say a GETPIVOTDATA function to populate the new field with the required total. Modify the formula to something like, =GETPIVOTDATA(whatever)<100 or whatever works for you to return TRUE or FALSE values to do the desired filtering. Use this new field as a page field in the pivot table setting it to TRUE or FALSE to suit. You will need to refresh the pivot table twice to get the desired result.

Or, and this is more advanced, you can do the filtering in SQL. So set up the pivot table from an external source (you can move it into your source file once created) with SQL something like below. Unchecked.

HTH. Regards, Fazza

SELECT VIN, Make, Model, YourMonth, Fuel
FROM YourData
GROUP BY VIN, Make, Model, YourMonth
HAVING SUM(Fuel)>=100