Set a Column in Pivot Table as Percentage of Another

Question

How can make a column to be a percentage of another column in pivot table? For example, in the following.

I want set the second column as the following percentages 1/1, 44/46, 459/465 etc. For the third column, I want it to appear as the percentages 1/1. 41/44 and so on.

Hambone · Accepted Answer · 2020-04-16T12:07:53.063

19

I believe what you want is a calculated field.

From the pivot table "Options" menu for Office 2010 or before or the "Analyze" menu for office 2013 or after, select "Fields, Items and Sets" -> "Calculated Field."

In the Name box, name it whatever you want ("% of SLA Met").

In the Formula Box, you can click on the fields to have them brought up, and it will end up looking something like this:

='Responded SLA Met'/'Ticket Nbr'

That said, you can't really do a "sum / count" -- you can, but it won't do what you want. So, you will probably have to add a column with all 1's in it (call it ticket count) and then use the sum of that as the denominator:

='Responded SLA Met'/'Ticket Count'

edited Apr 16 '20 at 12:07

answered Oct 18 '16 at 12:06

Hambone

15,600
8
46
69

That did the trick! Yes, I have to create a helper column `Ticket Count` like you wrote. – Concerned_Citizen Oct 18 '16 at 17:16
you are lifesaver – Amit Oct 29 '21 at 09:41

score 2 · Answer 2 · answered Sep 25 '19 at 09:41

2

In newer Excel menu Analyze -> Fields, Items, & Sets -> Calculated Field...

answered Sep 25 '19 at 09:41

sbrbot

6,169
6
43
74

score 1 · Answer 3 · answered May 13 '20 at 17:35

1

In excel 2016 is possible to put formula inside e.x.

=COUNT(id)/SUM(valid_license)

answered May 13 '20 at 17:35

Przemek

53
2
7

Set a Column in Pivot Table as Percentage of Another

3 Answers3