title: Powerpivot adding calculated item to a field
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>From this data I want to create a Pivot that would look like this:</p>
  
  <pre><code>        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
  </code></pre>
  
  <p>In PowerPivot, I want to add two additional columns, perhaps as calculated items in the Category field:</p>
  
  <pre><code>FoF=[Current Forecast]-[Prior Forecast]
  YoY=[Current Forecast]-[Last Year]
  </code></pre>
  
  <p>On below screenshots it is better visible, what I want to achieve:</p>
  
  <p><a href="https://i.stack.imgur.com/Tlb30.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Tlb30.jpg" alt="screenshot1"></a></p>
  
  <p><a href="https://i.stack.imgur.com/mUahc.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mUahc.jpg" alt="screenshot2"></a></p>
  
  <p>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)</p>