I have data in a table stored in an Excel file. I linked this table into a PowerPivot Data Model, and from that Data Model I want to create a Pivot in the same Excel file. In this table one column contains split of data into: Budget, Last Year, Prior Forecast, Current Forecast. I want to add this field as Pivot coulmns, but I would like to add additional calc item (like in normal Excel Pivot Table I can add Calculated Item) with calculation: [Current Forecast] - [Prior Forecast]. I checked various pages, forums, etc. and I have not found any guidance on how to add such a calc item to a field in PowerPivot. My input looks like this:
Sample data:
Category Client Amount
Current Forecast XYZ 600
Current Forecast ABC 1000
Current Forecast DEF 100
Prior Forecast XYZ 500
Prior Forecast ABC 1200
Budget XYZ 800
Budget ABC 900
Budget DEF 100
Last Year XYZ 700
Last Year ABC 500
From this data I want to create a Pivot that would look like this:
Current Prior Last
Client Forecast Forecast Budget Year FoF YoY
XYZ 600 500 800 700 100 -100
ABC 1000 1200 900 500 -200 500
DEF 100 100 100 100
In PowerPivot, I want to add two additional columns, perhaps as calculated items in the Category field:
FoF=[Current Forecast]-[Prior Forecast]
YoY=[Current Forecast]-[Last Year]
On below screenshots it is better visible, what I want to achieve:
I can't add this to my source data as the number of rows between current forecast and prior forecast are not in the same amount and not in the same order (they are extracted from another system)