Pivot table : absolute value of grand total in Excel

Question

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.

Solution

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.

Comments

[Excellll]

Does a "dynamic" solution rule out adding a column/field to your source data?

[Raystafarian]

If you're pulling a total, can't you edit the field settings to be absolute value?

[Karusmeister]

@Excelll - unfortunately yes. Raystafarian - abs is not available option in field settings

[Raystafarian]

How about if you insert a calculated field or a calculated item you could use the existing sum field and give it abs

[Doktoro Reichard]

Can you post a picture of the current layout or at least explain a little more about what you want to do?

[bwDraco]

Remember to properly format your question. Use a right angle bracket > before each line of a block quote, and indent code blocks with four spaces.