Pivot Table Subtotals/Variance Analysis
I am trying to get a pivot table to show a variance column for the subtotals of 2 sets of grouped data. This is for summarising all financial transactions by period (column) by funding source and cost centre (rows), and then comparing this to the budget amount. I have added a pivot table field using options to find the difference between these 2 groups (transactions and budget). It does return the correct calculation but also inserts a variance column for each individual column and I can't figure out a way to hide or disable the individual variance columns whilst leaving the subtotal variance column showing. As an alternative to the above I have also tried using the calculated fields option to create subtotals of the 2 sets of data but without success, and using calculated items inserts too many extra rows (for example funding source of capital shows all cost centres not just the cost centres that are capital funded). Any advice appreciated! -- NCW ------------------------------------------------------------------------ NCW's Profile: http://www.excelforum.com/member.php...o&userid=25993 View this thread: http://www.excelforum.com/showthread...hreadid=393568 |
Seems kind of simpleminded solution, but have you considered doing your
variance analysis outside the pivot table? BTW, the GETPIVOTDATA function can be handy for doing that. C^2 Conrad Carlberg "NCW" wrote in message ... I am trying to get a pivot table to show a variance column for the subtotals of 2 sets of grouped data. This is for summarising all financial transactions by period (column) by funding source and cost centre (rows), and then comparing this to the budget amount. I have added a pivot table field using options to find the difference between these 2 groups (transactions and budget). It does return the correct calculation but also inserts a variance column for each individual column and I can't figure out a way to hide or disable the individual variance columns whilst leaving the subtotal variance column showing. As an alternative to the above I have also tried using the calculated fields option to create subtotals of the 2 sets of data but without success, and using calculated items inserts too many extra rows (for example funding source of capital shows all cost centres not just the cost centres that are capital funded). Any advice appreciated! -- NCW ------------------------------------------------------------------------ NCW's Profile: http://www.excelforum.com/member.php...o&userid=25993 View this thread: http://www.excelforum.com/showthread...hreadid=393568 |
All times are GMT +1. The time now is 11:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com