1

I have a pivot table in which I want to show on time delivery %:

SO26642184 question example

My "on time lines count" is the total amount of lines I have shipped on time. The total lines count is the total lines count regardless if it has shipped on time or not. For example my on time delivery % on month 1 should be 79.8% which is 1227/1537.

How can I get this to show inside of my pivot table.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3067028
  • 159
  • 3
  • 5
  • 15

3 Answers3

1

Add a Calculated Field with Formula: 

=On Time Lines Count / Total Lines Count
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • I don't want to do that, because If I do, then everytime I refresh the data, it causes my formulas to move up or down and then gives me errors. – user3067028 Oct 29 '14 at 23:16
  • I looked at the links you provided. I don't see PivotTable Tools > Options > Tools - Formulas, Calculated Field. I don't see options under my pivot table. The options I do see to the far left doesn't have a tool selection under it. By looking at what the link, that seems to be exactly what I need, I just can't find it on my excel. – user3067028 Oct 30 '14 at 14:50
  • 1
    Hey pnuts. I found it. For me it's under Pivot Table Options --> Fields,Items, & Sets --> Calculated Field. So this worked for me once I found out where it was. Thanks alot! – user3067028 Oct 30 '14 at 16:25
0

If I were in that situation, I'd probably put a formula like the following all the way down Col D (assuming the pivot table is in cols A:C):

=if(c6="","",b6/c6)

That will show the percentage you want where it is relevant and empty cells elsewhere, and will continue to do so when the pivot table is refreshed.

MattClarke
  • 1,647
  • 1
  • 11
  • 32
0

To solve the problem of your PivotTable, you can create a calculated field and format as a percentage, this will work, I reproduce your example and it worked...

Link example

Mega

Lucas Motta
  • 135
  • 2
  • 11