title: Multiple Calculated Columns with Filters within Single Pivot Table
body: <p>I am working to create a Pivot Table in Excel that has multiple calculated fields that take into account whether a condition is true, but cannot figure out how to create this within a single Pivot Table.</p>
  
  <p>Behind the scenes, I have a set of data with a column that can have these values: A,B,C,D,F,L, or R.
  I have another column that is a dollar value, and another for Quantity.
  I am trying to calculate the price per unit when the Category is A,B, or C, but also calculate the price per unit when the Category is D.</p>
  
  <p>I can create multiple Pivot Tables and tie them to the same slicer, but the issue is this: I need to display the spending in each of the two categories for each location, and when I create two separate Pivot Tables for this, locations that do not have spending in one of the categories are excluded (in this below case, location 2 has no spending in Category D, so it does not show up in the second Pivot Table).</p>
  
  <p>Here is a portion of my data set. The whole data set is over 100,000 rows and will change over time, so I do need a solution for the long-term.</p>
  
  <p><code>Location Category   Volume     Quantity</code></p>
  
  <p><code>1        A          $120.32     6000</code></p>
  
  <p><code>3        A          $30.08      1300</code></p>
  
  <p><code>3        A          $60.16      2600</code></p>
  
  <p><code>1        B          $39.91      1000</code></p>
  
  <p><code>2        B          $318.50     13000</code></p>
  
  <p><code>2        C          $196.00     8000</code></p>
  
  <p><code>1        D          $220.50     8100</code></p>
  
  <p><code>3        D          $171.50     6300</code></p>
  
  <p><code>3        D          $35.90      1000</code></p>
  
  <p><code>3        D          $53.85      1500</code></p>
  
  <p><code>2        F          $416.50     0</code></p>
  
  <p><code>1        L          $24.50      0</code></p>
  
  <p><code>2        L          $30.08      0</code></p>
  
  <p><code>1        R          $55.13      0</code></p>
  
  <p><code>2        R          $55.13      0</code></p>
  
  <p><code>3        R          $110.26     0</code></p>
  
  <p>Thanks in advance for your help and let me know if I need to clarify anything!</p>