Calculated item in pivot table #Num! error message

[INN]

Hi
I have added a calculated item to the pivot table and it was ok until I added a calculated column then I got #Num! for each calculated item, why is that? Please see attached file as well. Thank you so much

Book2.xlsx

Sheet1 Region,Month,Sales South,Feb,717 South,Jan,947,Region,Month,Sum of Field1 North,Jan,827,East,Feb,2740 East,Feb,974,Jan,3290 East,Jan,945,East Total,6030 South,Jan,609,North,Feb,2410 South,Feb,822,Jan,3200 South,Jan,647,North Total,5610 North,Jan,927,South,Feb,3180 West,Feb,734,Jan,3620 South

docs.google.com

Book2.xlsx
	A	B	C	D	E	F
1	Region	Month	Sales
2	South	Feb	717
3	South	Jan	947		Region	Sum of Field1
4	North	Jan	827		East	6030
5	East	Feb	974		Jan	3290
6	East	Jan	945		Feb	2740
7	South	Jan	609		%change	#NUM!
8	South	Feb	822		North	5600
9	South	Jan	647		Jan	3200
10	North	Jan	927		Feb	2410
11	West	Feb	734		%change	#NUM!
12	South	Jan	703		South	6800
13	East	Jan	518		Jan	3620
14	North	Jan	804		Feb	3180
15	East	Feb	819		%change	#NUM!
16	East	Feb	949		West	2330
17	East	Jan	622		Jan	630
18	West	Feb	970		Feb	1700
19	South	Feb	528		%change	0.0000%
20	North	Jan	637
21	South	Feb	608
22	North	Feb	996
23	West	Jan	629
24	East	Jan	650
25	North	Feb	828
26	East	Jan	552
27	North	Feb	586
28	South	Feb	502
29	South	Jan	714
Sheet1

 

[Alex Blakenburg]

There are a couple of ways of doing this but they will both end up looking slightly different to the way you are trying to present it.
Option 1) Pull your sales column into your Pivot Values field a second time and then Show Values as > % Difference from (Previous)

It will look something like this:

Option 2) Because the months are in the same field or column, you can't use Calculated "Field" you need to use Calculated "Item"

The issue with that is that since it is now a % in the same field the subtotals and totals don't make sense and you would need to remove them.
Arguably adding the months in a comparison scenario doesn't make sense anyway.

 

[INN]

Thank you. You answered more questions than I asked. So I learn other stuff as well. Thank you again.

 

[Alex Blakenburg]

Appreciate the feedback. Glad I could help.