Pivot table remainder of total

[magnus101]

Hello,
Is there a way to add a field in a pivot table, that is the remainder of the total?
I see there is a running total, % of grand total.

But is there a remainder of total?

so the total is 100
row one has a running total of 10, so the remainder of total is 90
row two has a running total of 25, so the remainder of total is 75
etc...

thanks

 

[Andrew Poulsom]

Can't you add another column to your source data that calculates it?

 

[magnus101]

When I add a calculated field, I only see the columns of the table, the pivot table is pulling from.
It does not show my running total field.

 

[Andrew Poulsom]

No, add a column with formulas to your source data and add that column to your pivot table.

 

[magnus101]

I do not think that will work.
I am building a burn down chart. Where I do counts of completed based on date.

 

[Andrew Poulsom]

Please post a small but representative sample of your source data and a description of what you would like to see in the pivot table.

 

[magnus101]

Thank you for getting back.

This is what I would like at the end. A burndown chart.

My data is like this

Store       Project_Status       installDate
7934    Pending     
9682       Completed    7/24/2012
3131   Completed    7/30/2012
7739    Completed    8/2/2012
4214    Completed    8/6/2012
4664    Pending    
3764    Completed    8/7/2012
7882    Pending    
5103    Pending    
7881    Completed    8/8/2012
5836    Completed    8/8/2012
4649    Completed    8/9/2012
37    Pending    
2011    Completed    8/11/2012
3367    Completed    8/13/2012
3009    Pending

Completed has a date. Pending has no date.
And the data can grow.



through a pivot table I can get a running total.

which gives a chart like this

Which is the opposite of what I want. This is counting up. Instead of counting down.

any ideas?

thank you

 

[Andrew Poulsom]

In D1 enter a heading eg Remainder. In D2 enter the formula:

=IF(B2="Completed",COUNTIF(C$2:C$17,">"&C2)/COUNTIF(C$2:C$17,C2),0)

and copy down. Add that field to your pivot table as Normal Sum.

 

[magnus101]

Thank you. I will give it a try