Pivot Table % Difference From, when base value is negative

[jchurley]

Hello and thanks in advance for your help.

I have a data set and pivot table that contains profit numbers over multiple years. I'm trying to get the report to display not only the profit numbers for 2 years, but also the year over year change - both in dollars and percent.

I'm basically dumping the profit field three times - the first time just sums the field, the second time shows the field as "Difference From" the last year number, and the third time shows the field as "% Difference From" the last year number.

The first two are no problem, but I can't get the third (% change year over year) to calculate properly all of the time - specifically when the last year number is negative (a loss). The % Difference From option divides the difference by the base field so since the base field is negative, the difference is "reversed" - losing more money shows as a positive % improvement since a negative divided by a negative is positive, and losing less money shows as a negative % since a positive divided by a negative is negative.

I've tried many variations of calculated fields and showing values differently but I can't get this to work. Any ideas?

I'm including a small example to illustrate:

[TABLE="width: 159"]
<tbody>[TR]
[TD="colspan: 2"]DATA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]City[/TD]
[TD]Year[/TD]
[TD]Profit[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]This Year[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]This Year[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]This Year[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]This Year[/TD]
[TD="align: right"]-100[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Last Year[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Last Year[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Last Year[/TD]
[TD="align: right"]-75[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Last Year[/TD]
[TD="align: right"]-70[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 503"]
<tbody>[TR]
[TD]Pivot Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Sum of Profit[/TD]
[TD="colspan: 2"]Sum of Profit2[/TD]
[TD="colspan: 2"]Sum of Profit3[/TD]
[/TR]
[TR]
[TD]City[/TD]
[TD]This Year[/TD]
[TD]Last Year[/TD]
[TD]This Year[/TD]
[TD]Last Year[/TD]
[TD]This Year[/TD]
[TD]Last Year[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]           60[/TD]
[TD]          70[/TD]
[TD]          (10)[/TD]
[TD][/TD]
[TD="align: right"]-14.29%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]          100[/TD]
[TD]          80[/TD]
[TD]           20[/TD]
[TD][/TD]
[TD="align: right"]25.00%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]          (50)[/TD]
[TD]         (75)[/TD]
[TD]           25[/TD]
[TD][/TD]
[TD="align: right"]-33.33%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]         (100)[/TD]
[TD]         (70)[/TD]
[TD]          (30)[/TD]
[TD][/TD]
[TD="align: right"]42.86%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Notice how the % change for city C is negative despite the fact that they improved - lost less money - and vice-versa for City D.

Thanks for any suggestions.

joe

 

[BrianMH]

Don't use a calculated field use a calculated item.

For the difference field use the formula

='This Year' -'Last Year'

For the difference % field use the formula

=('This Year'-'Last Year')/ABS('Last Year')

Excel Workbook
	F	G	H	I	J	K
3	Sum of Profit	Column Labels	*	*	*	*
4	Row Labels	Last Year	This Year	Difference From	Difference From %	Grand Total
5	A	70	60	-10	-14.29%	119.8571429
6	B	80	100	20	25.00%	200.25
7	C	-75	-50	25	33.33%	-99.66666667
8	D	-70	-100	-30	-42.86%	-200.4285714
9	Grand Total	5	10	5	1.19%	20.01190476
Sheet1

 

[jchurley]

Thanks for the response. That works great with my made up data where the data is in the spreadsheet, but my real file links to data in an Access table and apparently you can't create Calculated Items in a pivot table that references data in Access.

Any help is appreciated.

Joe

 

[BrianMH]

Can you pull the data into a data table in excel and then point the pivot at that?