Pivot Table % of next column

[koskesh]

I have a pivot table and I'm not sure how to handle one task:

I have 4 columns for each week.

Actual Shipment (Value)
Forecast (Value)
Actual Shipment in % of the Forecast
Whats left (Actual Shipment - Forecast in %)

The first two columns are simple drag&drops. But cant figure out a way to display the columns in %.

I uploaded the file. I guess its easier to look at the data.

 

[jeffreyweir]

Hi Koskesh. One of the problems you have is that you are trying to make a pivot table out of a crosstab instead of a flat file. You need to convert your raw data so that it is 'pivot compliant'.

When you've done that, after you make a PivotTable out of it it will look like this:


...instead of this:


THen you can do what you need to:



For more info, google these terms:
Pivot Flat File Crosstab

I posted some code to do this at http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/
(Look for the last code listing under the "And the winner is..." section)

You can find other code that does the same thing here:
http://dataprose.org/category/unpivot/

 

[jeffreyweir]

Here's a sample workbook showing you the difference.

 

[koskesh]

thank you!