Pivot Table

[sogc]

I have a pivot table with a column of positive and negative values. I want to create a calculated field to sum up the positive values only. However, when I do that, the subtotals are not taking the If statement of the calculated field into account and is simply summing up the field to the left that contains both the positive and negative values. Here is my code for the pivot table:

= if ( GrossMargin <0, 0, GrossMargin )

The data itself is correct. Wherever GrossMargin is negative, my new field shows zero. Wherever GrossMargin is positive, my new field displays that positive value. However, the subtotal for my new column sums both the positive and negative values.

Any ideas.

 

[SkipVought]

Hi,

Do you calculations in the Source Data Table.

Resize the Source Data Range in the PT Wizard and sum the new column.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[sogc]

Nice thought. However, that won't work, because the negative number is based on the sum of the gross margin and not the gross margin of the individual rows in the source date.

I would get a different answer if I did an IF statement on the raw data than on the sum of the raw data.

Are you suggesting that doing an IF formula in a calculated field of a PivotTable exposes a bug in the sub-totals of that calculated field?

 

[SkipVought]

Please post some sample Source Data that illustrates this situation, along with the formula that you are using in the PT.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[sogc]

See example at

http://www.digiuseppe.ca/files/pivottableexample.xls

 

[SkipVought]

Two new columns
[tt]
Gross_Margin Pos_GM
=Sales_ -Cash_Prizes_-Cheque_Prizes_ =IF(Q2>0,Q2,0)
[/tt]
using named ranges.

PT Results...
[tt]
Sum of Gross_Margin Sum of Pos_GM
-6285 1561
-6285 1561
-55 0
5853.5 5853.5
5798.5 5853.5
-486.5 7414.5
[/tt]


Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]