Pivot table - show detail for only negative grand totals

[josterma]

Good day,

I have a pivot table based on about 12,000 rows of data. The source data includes both positive and negative values so that some of the Grand Total lines in the pivot are negative and some are positive (and some are zero in the cases where the positives and negatives exactly offset each other). I would like the end user to be able to simply and easily show the Pivot table detail in one spreadsheet for only the lines with negative Grand Totals.

I have been able to sort/group the negative values together in the pivot table by going to Field Setting->Advanced and changing the Ascending selection to match the correct field.

However, I am still limited to only clicking on one Grand Total value at a time in order to show the detail - and this will be too tedious for my end user. Also, please note that the pivot table will be updated often and the number of columns and rows will be expanding over time. So, a macro that only addresses values as of today would be insufficient.

Your counsel is much appreciated.

Thanks,
Jeff

 

[c_m_s_jr]

Once option is to actually use auto filter. Highlight the row with your column headings in the pivot table plus one column to the right (you must start at the right or auto filter will not be an option).

Example: If you pivot table is in columns A - D then highlight column E - A and select auto filter. You will then have the ability to filter for all rows in your grand total column that is less than 0.

 

[josterma]

Thanks - this is a helpful hint for the sorting.

I don't think it gets me any closer on easily showing the detail for all the negative values.

Any thoughts?

 

[Fazza]

Hi, Jeff.

The question would be easier to answer with a more definitive description, such as would be available with some sample data.

As I understand, the requirement is to show only data where the sub-totals are <0.

I guess this is for just one particular field? Or is this sort of functionality required for which field the user chooses. I'll assume for just one fixed/known field. I'll call that field "Account" for the purpose of description, and the field being summed "Cost".

One approach might be to have a second pivot table giving the sub-total by Account. And then add one new field, say "ShowIt" to the source data with a simple GETPIVOTDATA<0 to return either TRUE for sub-totals < 0 and false for others. Then in the original pivot table, make this new field a page field and set it to show the TRUE data. Now to get the result, first refresh the new pivot table and then the original pivot table. So, two pivot table refreshes each time. There will be a bit of memory required for this.

A neater way, without an extra pivot table and without adding an extra field to the data is via SQL. Such as, in a new workbook start the pivot table wizard and at the first step take the external data option. Proceed and connect to the source data (easiest if you give the source data a normal [not dynamic] named range before starting) and continue into MS Query. Then hit the SQL button and edit the SQL to be like below. Untested. Please modify to suit your table & field names,

SELECT *
FROM YourTable A,
(SELECT B.Account
FROM YourTable B
GROUP BY B.Account
HAVING Sum(B.Cost)<0) C
WHERE A.Account = C.Account

Exit MS Query with the 'open door' icon and complete the pivot table. The completed pivot table can now be moved back into the source file if you wish. This single pivot table does the job and is refreshed in the normal manner.

HTH, Fazza