title: Pivot table calculate field based on grouped values
body: <p>after years of quietly learning from this site I've finally hit a question who's answer I cannot seem to find on StackOverflow...</p>
  
  <hr>
  
  <p>I have a pivot table that needs to calculate Net Promoter Score from several groups within a population.</p>
  
  <p>Net promoter score is calculated like so:</p>
  
  <pre><code>[% of Population that give 9 or 10/10] - [% of Population that give 1 to 6/10]
  </code></pre>
  
  <p>Each individual record in my source data can only have a single Score of between 1 and 10:</p>
  
  <pre><code>RAW DATA:
  Date (dd/mm)    Country    Type         Score (1-10)    NPS Category
  01/05           US         Order enq.   9               Promoter
  13/05           US         Check-out    5               Detractor
  28/05           US         Order enq.   7               Passive
  </code></pre>
  
  <p>So, with help from the answers below I've added a column to categorise each individual into the Promoter (9 or 10), Passive (7 or 8) and Detractor (1 to 6) groups based on that score: <a href="https://cloud.githubusercontent.com/assets/9369383/9228693/8d08fb12-4111-11e5-91ad-aabfc710f1b7.png" rel="nofollow">screenshot of raw data</a> (with sensitive items hidden).</p>
  
  <p>All that remains now is:
  How can I create a calculated 'NPS' column like the one shown in my (rudimentary) representation of a pivot table below that takes the Detractor value from the Promoter value?</p>
  
  <pre><code>D = Detractor group
  Pa = Passive group
  Pr = Promoter group
  
  
        |     Order enquiry    |      Check-out      |
        | D    Pa   Pr   NPS   |  D    Pa   Pr   NPS |
  -------------------------------------------------- |
  GB    |                      |                     |
    May |  0    0  100    100  | 30    20    50   20 |
    Jun | 10   30   60     50  | 35    35    60   25 |
    Jul | 30   20   50     20  | 40    10    40    0 |
  US    |                      |                     |
    May | 45   15   40    - 5  | 50    10    40  -10 |
    Jun | 40   30   30    -10  | 40    30    30  -10 |
    Jul |  5   35   60     55  | 20    40    40   20 |
  </code></pre>
  
  <p>My attempt at a calculated column can be seen in this <a href="https://cloud.githubusercontent.com/assets/9369383/9221907/bf1f698c-40e5-11e5-9409-6ce82ae2762f.png" rel="nofollow">screenshot</a>. This results in an error and of course I haven't managed to convert the NPS counts into percentages yet.</p>