Create a variance column in a Pivot Table - Excel 2010

This seems to simple, but I have a pivot table that will only hold the data for two months, that is the end of the month data for say, 8/31/2011 and a column for 9/30/2011.  All I want is to add a third column with the variance between the two, but this seems impossbile in Excel 2010.  
Sandra SmithRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ScriptAddictCommented:
This should give you all the information needed.  If you have any questions after reading the article just post again.


MS Excel 2010 custom calc on pivot tables
0
ScriptAddictCommented:
A little more detail on the above solution.

You'll need to add the same data field twice.  The first one you leave as normal.

The second one you'll adjust as per this image.

 Image of how to do it.
0
Sandra SmithRetiredAuthor Commented:
Ok, I think that is where I am getting stuck, adding the same field twice. Variance Variance
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Sandra SmithRetiredAuthor Commented:
Sorrry, I added it twice.
0
Sandra SmithRetiredAuthor Commented:
I keep getting N/A in both columns now.
0
ScriptAddictCommented:
Ok here is what I did, and it worked.

Start your pivot table as normal
Whatever you are summing for your data that's the field you want to enter 2x.

The first time you want to enter it as normal and set value to sum or whatever

The second time you want to change the field value, and click on the show values tab and choose previous.  

I've posted something that you can look at as an Excel file.
 Variance-in-PivotTable-Excel-201.xlsx
0
Sandra SmithRetiredAuthor Commented:
ScriptAddict, let me try your suggestion.  I have a meeting so it will be a bit before I can return to this.
0
Sandra SmithRetiredAuthor Commented:
Ok, I have the second column, but it is giving me the difference of the value in the same column.  That is, the (9,711.00) value is the difference between 65,797 and 56,086 in the first few rows.  I need teh different between 65,797 and 63,055.
 Variance
0
ScriptAddictCommented:
So close!  We've almost got it!  

Go back to where you selected previous on the second column.  

You need to change your base field to whatever your month column is called.  
I think it is on whatever your item column is named.

When you are there you might want to change the Custom name to more accurately describe the field.  ie. Variance from Prior Month Sales
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Works, below is the final version!  Now, if I can just remember how I did it for the next time!  Also, need to code this all, but at least i is possible.  I as getting concerned that it could not be done!  Thank you.. Variance
0
Sandra SmithRetiredAuthor Commented:
Now to get this all coded in VBA!

Sandra
0
Sandra SmithRetiredAuthor Commented:
Ok, showed the user and now they also want a percentage variance column.  If I have problems, will post a related question.

Sandra
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.