Remove extraneous rows caused by calculated item

[SterlingP]

Greetings,

I want to suppress extraneous rows and columns resulting from inserting calculated fields or items into a pivot table.

I start with data like this:

Then create a pivot table like this:

I want to insert a %Difference column to the right of "TY". I added a calculated item with the formula =(TY-LY)/LY. It does the calculation, but ends up looking like this:

Is there a way to suppress or hide the items in red? I am in real danger of tearing out what little remains of my hair in trying to solve this.

Please note that I have already posted this at I have already posted this question at http://episteme.arstechnica.com/eve/forums/a/tpc/f/99609816/m/299006031831 and have not found a solution.

Thanks!

 

[sunnyland]

Hello there,

I used your data source but rather than using your formula, you should use [when they exist the one building pivot]

TYLY.xls
	A	B	C	D	E	F
13
14
15				TYLY
16	Region	District	Data	LY	TY	GrandTotal
17	4	6	SumofSales2		600.00%
18			SumofSales	20	140	160
19		10	SumofSales2		42.86%
20			SumofSales	1400	2000	3400
21	5	2	SumofSales2		20.00%
22			SumofSales	100	120	220
23		9	SumofSales2		-1.38%
24			SumofSales	800	789	1589
25	TotalSumofSales2				31.42%
26	TotalSumofSales			2320	3049	5369
Sheet1

My sumofsales2 give me the same result that your formula 1 without the problem with the div/0 error.

Just drag the sales in the middle data items then select
Under data the sumofsales2 row:by selecting one it should select all occurences of them and they should be highlighted.

Right click and select field options...

If you click on options [usually it default to normal] you will see more options one of them being:
% difference from
then select baseitems : tyly
then select basefiels: Ly

and this is it: no error:

you have to drag another instance of your sales to have like me the details
%

 

[SterlingP]

Francoise, thanks for the response...it worked perfectly!