title: Create a "dynamic" calculated item in pivot table
body: <p>I have a set of data that looks like below:</p>
  
  <pre><code>Business Unit  Year  Customer   Sales$   GP$
  Beverage       2011  ABC1       500      60
  Beverage       2012  ABC1       500      60
  Beverage       2013  ABC1       500      60
  Beverage       2014  ABC1       500      60
  Beverage       2015  ABC1       500      60
  Property       2011  ABC1       500      75
  Property       2012  ABC1       500      75
  Property       2013  ABC1       500      75
  Property       2014  ABC1       500      75
  Property       2015  ABC1       500      75
  Retail         2011  ABC1       500      60
  Retail         2012  ABC1       500      60
  Retail         2013  ABC1       500      60
  Retail         2014  ABC1       500      60
  Retail         2015  ABC1       500      60
  </code></pre>
  
  <p>What I'm trying to do is to create a by <code>Business Unit</code> by <code>Year</code> pivot table with  sales, <code>GP</code> and <code>GP%</code> (<code>GP%</code> is a calculated field) on the row label, the desired format will be like this (the columns highlighted in yellow is the format I want to add to the pivot table):</p>
  
  <p><img src="https://i.stack.imgur.com/EiRBw.png" alt="Pivot table with year subtotal"></p>
  
  <p>I can get it done by adding a calculated item on the column <code>Business Unit</code> to sum the sales, <code>GP</code> for all 3 business units in respective year, and the calculated field for <code>GP%</code> can be shown correctly, but the issue is that when I only choose to see any 2 of the business units, the calculated item on the column still show the results of the 3 business units because the formula I type is </p>
  
  <pre><code>=Business Unit[Beverage]+Business Unit[Property]+Business Unit[Retail]
  </code></pre>
  
  <p>so instead I tried to use relative reference in the formula by</p>
  
  <pre><code>=if(iserror(Business Unit[-3]), Business Unit[-2]+Business Unit[-1],Business Unit[-3]+ Business Unit[-2]+Business Unit[-1])
  </code></pre>
  
  <p>the <code>if</code> formula indeed worked but the fact is I have 20+ business units in my real data, and the number of characters I input in the formula box will exceed 255. </p>
  
  <p>I'm now clueless on how to show the correct subtotal column for the year in the pivot table. I searched in the internet and one of the workaround to this is add some columns and formula next to the pivot table but its too clumsy as I have lots of formatting in the pivot table and slicers. Can it be done via powerpivot, if yes....can you be kind enough to specify the steps as I have no idea on how to use powerpivot. Any suggestions are highly appreciated! Thanks!              </p>