Pivot table manipulation question

[sa02000]

I have incoming data in certain format (Sheet Jan in attached file). In this data I have first three columns as categories and rest of the columns to the right are numbers.

I would like to get results using pivot table as shown in the second sheet in the attached file. I can get part of this correctly but I can't figure out how to do the math that I need to do on the second category column.

I know how to add a "calculated field" but I need this calculation for every column in pivot table. I almost need to somehow do this calculation on "page" of the pivot table.

Any ideas on how to do this in excel or another way to do this without anything cumbersome -- Entering formulae will be very long process (as I won't be able to copy paste formula and if rows don't line up any new month then those will have to be redone), creating three different sheets to separate that data and then doing the formulae will again will make file size very large and slow.

Thanks in advance,
Jay

[matrosov]

I don't think you can accomplish this with a pivot table because of the way your data is laid out. To get what you want with calculated field feature your sub cats need to be in columns not in rows. I.e. you have a column for subcat1 subcat2 etc. You can create a pivot so all your categories show up as columns then copy and paste that as value to be your new data file and then you will be able to use a calculated field.