Excel – Pivot table : absolute value of grand total in Excel

microsoft excelmicrosoft-excel-2010pivot tablesorting

My grant total is generated by summing values of COLUMNS of ITEMS for a given SUBJECT (For each SUBJECT I have a list of ITEMS and I want to make a grand total of the values of these ITEMS). The values for the items can take negative values and my grand total should be the sum of positive and negative values. I need to select top 20 rows according to the absolute value of grand value. The solution has to be dynamic – I can't copy value from Pivot Table to separate sheet and sort it there. I tried to define “Calculated Field” as =ABS(SUM(VALUE)) but it sums absolutes of a values for ITEMS.

Best Answer

This issue is identified by Microsoft... see http://support.microsoft.com/kb/211470/en-us.

But I think a way to achieve this is to use an calculated item in the pivot. For example, if you have a dataset like this :

id | value
--
1  | 300 
--
2  | -400
--
3  | -500
--
4  | 600
--

A calculated item with this formula : =SUM(ABS(id[1])+ABS(id[2])+ABS(id[3])+ABS(id[4])) will produce a result of 1800, instead of 0 with the pivot table grand total. Notice that you need to have an "id" column in your data set. In the calculated field, id[i], with iindicate the item number as appears in the pivot table. For your case you have to iterate the formula until 20.

If you have a lot of rows or if the number of rows can vary, you could look to a macro for build automatically the calculated item.

Related Question