CODEWITHSUNDEEP
excelpivot-tableexcel-tables
4lackof
4lackof

Reputation: 1390

Excel Pivot Chart - % Of Two Values

Let's say I have the following table:

CORP | COAST | CITY | DONE | MISSING | TOTAL
-------------------------------------
New  | West  | LAX  | 2    | 4       | 6
Old  | West  | SFO  | 3    | 3       | 6
New  | East  | NYC  | 4    | 2       | 6

I make a pivot table that looks like this:

                   CORP:
COAST:             NEW | OLD
-------------------------------------
EAST                   |
  SUM OF TOTAL    | 6  |
  SUM OF DONE     | 4  |
  SUM OF MISSING  | 2  |
WEST
  SUM OF TOTAL    | 6  | 6
  SUM OF DONE     | 2  | 3
  SUM OF MISSING  | 4  | 3

I want to now add a ROW that is the percentage DONE of the TOTAL amount. I.e. something like this: 

                   CORP:
COAST:             NEW | OLD
-------------------------------------
EAST                   |
  SUM OF TOTAL    | 6  |
  SUM OF DONE     | 4  |
  SUM OF MISSING  | 2  |
  % DONE          | %67|
WEST
  SUM OF TOTAL    | 6  | 6
  SUM OF DONE     | 2  | 3
  SUM OF MISSING  | 4  | 3
  % DONE          | %33| %50

The formula for % DONE is DONE / TOTAL

I've tried adding another DONE field to the pivot table (i.e. SUM OF DONE2) then setting the value to be % OF, the base field TOTAL and the base value (previous) however that makes % DONE return the value of #N/A. I've tried various combinations of the above however all are returning the same #N/A. I.e. my pivot table is looking like this:

                   CORP:
COAST:             NEW | OLD
-------------------------------------
EAST                   |
  SUM OF TOTAL    | 6  |
  SUM OF DONE     | 4  |
  SUM OF MISSING  | 2  |
  % DONE          |#N/A|
WEST
  SUM OF TOTAL    | 6  | 6
  SUM OF DONE     | 2  | 3
  SUM OF MISSING  | 4  | 3
  % DONE          |#N/A| #N/A

I've tried Googling how to do it but still to no avail. Please let me know if what I am looking for is possible.

Upvotes: 1

Views: 39

Answers (1)

Greg Viers
Greg Viers

Reputation: 3523

On the Analyze tab, add a Calculated field with the formula 

 = 'DONE'/'TOTAL'

enter image description here

There is an option in Pivot Table Options that allows you to show blank if there is an error, so you don't get an N/A for old/east. You can go into field settings and format the field as a percentage.

Upvotes: 1

Related Questions