Remove extraneous rows caused by calculated item

SterlingP

New Member
Joined
Oct 6, 2006
Messages
15
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:

pivot1.jpg


Then create a pivot table like this:

pivot2.jpg


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:

pivot3.jpg


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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
ABCDEF
13
14
15TYLY
16RegionDistrictDataLYTYGrandTotal
1746SumofSales2600.00%
18SumofSales20140160
1910SumofSales242.86%
20SumofSales140020003400
2152SumofSales220.00%
22SumofSales100120220
239SumofSales2-1.38%
24SumofSales8007891589
25TotalSumofSales231.42%
26TotalSumofSales232030495369
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
%
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top