Excel PivotTable filter: how to calculate percentage?

January 6, 2017 8:57 AM

One field, FieldA, is being summed in a column. Another field, FieldB, is being counted in a column. How do I get FieldA/FieldB as a percent in the pivot table?

Sample PivotTable:

REGION      # APPLICATIONS      # APPROVED      % APPROVED

ALABAMA          12                  10            83.3%

KENTUCKY         10                   5            50.0%

MARYLAND          4                   1            25.0%

The data upon which the pivot table above is built has a row for each application. The # APPLICATIONS, therefore, is a count of the unique field APPLICATION NUMBER.

There is another column in the data that shows the decision on the application. This field contains such values as "APPROVED", "DENIED", "PENDING", etc. Next to this column I've made my own column "APPROVED", which shows a 1 if the application was approved and a 0 if it was any another decision.

So, to get the # APPROVED in the pivot, I've summed that column "APPROVED". How do I go from here and get that last column, % APPROVED, to calculate within the pivot table? I've played around with all the summarize values by/show values as options and I can't figure it out.

I realize I can place a static formula out to the side of my pivot table but I don't want that.

Thanks in advance for the help!

posted by bologna on wry to Technology (5 answers total)

You can do a calculated field in your pivot table! I don't know what version of Excel you're using so I can't tell you where to find it, but somewhere in the pivot table options there should be "field" or "calculation" options and you should be able to find an option to make a calculated field. In there, you can define the field "% approved" as approved/applications, and then add it to your table.
posted by brainmouse at 9:09 AM on January 6, 2017 [1 favorite]

I don't have a way to do this in the pivot table itself (though I will watch this question to see if there is one), but I have three suggestions:

1) Create a column containing this percent within your source data table, and then simply have the pivot table fetch it with "average". Depending what your source data table looks like, you can probably create this column in your source data table with a combination of COUNTIFS and SUMIFS. If you provide sample data I can probably help with this formula.

2) Create a column next to the pivot table and simply calculate it as =#approved/#applications (format as %). You would need to re-do this row manually, if, e.g. you added more regions or otherwise changed the shape of your pivot table.

3) Do this in a database. This would be very simple in Access or any other relational database software you care to use.
posted by agentofselection at 9:49 AM on January 6, 2017

Oops, I didn't read all of the question. Okay, you don't like my Option 2. Option 1 and Option 3 could probably work, though.
posted by agentofselection at 9:52 AM on January 6, 2017

Best answer: 1. Add a column "Total" to your data that's just a 1 in every row.
2. Refresh your pivot table so it picks up the new data then click on your pivot table, click "options" then "Fields, items & sets" then click "calculated field" (your menu may vary if you have a different version of Excel)
3. Rename "Field1" to "% Approved"
4. Using the "Insert Field" buttons, replace the formula "= 0" with "= Approved/Total" (no quote marks)
5. Click Add then OK
6. "% Approved" will now show up in your field list. Drag to the bottom right "Values" corner; set it to show the sum.

This should work-- I just tried it with your data and got the correct numbers.
posted by acidic at 9:56 AM on January 6, 2017

Response by poster: Thanks everyone!

acidic, that was what I was looking for - worked like a charm! Can't believe I didn't think of it. Thank you so much!
posted by bologna on wry at 10:06 AM on January 6, 2017 [1 favorite]