Add a calculated field in Excel Pivot table

I am using Excel 2010. I have a base table which has month, gender and cost columns. Month is like Jan, Feb, Mar... Dec. Gender is Male or Female.
I need to create a pivot table, which can get the sum of cost by Males and Females for each month. Then again, the Female cost should be subtracted from the Male sum each month.
The cost should be in values field, Gender should be in Column Labels and Month is Row label.
How can I do that. Please suggest a method.
vsuripeddiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

vsuripeddiAuthor Commented:
Please provide solution
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Upload a sample workbook with enough data and mock the desired output manually to show what output you are expecting.
vsuripeddiAuthor Commented:
Hi

Please find the excel file pivot.xlsx  attached.
It has a raw data by month and also the final pivot format that I am expected. I had highlighted the
expected pivot values in yellow.
Please provide solution.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think you forgot to attach the file.
vsuripeddiAuthor Commented:
vsuripeddiAuthor Commented:
sktneer,

I had attached for my file.  I apologize for the mistake earlier .
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
See the attached if this is what you are trying to achieve.
pivot.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the workbook with the steps involved to insert the Calculated Item Field in the Pivot Table.
Steps-to-Insert-Calculated-Item-in-Pivot
vsuripeddiAuthor Commented:
Sktneer

Thanks for the post. Unfortunately, my winzip is not working properly. Would you please send me the actual Excel file. Sorry for the trouble.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I am confused. I sent you the excel file not the zipped file so you just need to download the file and open it. That's it.

What excel version you are using?
Did you try downloading the second file I uploaded with the steps you will need to follow to achieve the task?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Oh I see. I think there was an error while uploading the file.

I am trying to attach it again, hope it will be ok this time.
Steps-to-Insert-Calculated-Item-in-Pivot
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No. Something wrong.
I will try again. lol
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:

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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Failed in my last two attempts.

Trying it once again.
pivot.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Do you get the pivot.xlsx this time?
vsuripeddiAuthor Commented:
sktneer

Thanks a bunch for your post.

I did get ur file this time and am trying to follow ur instructions.
As per your suggestion, I am supposed to go into Pivot table Options and then to Fields, Items and Sets and within that click on the Calculated Item.

I tried to follow that instruction, but the "Calculated Item" option is disabled. Only "Calculated Field" option is enabled. Any suggestions?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That means you didn't read all the steps carefully.
See step 14 where I also mentioned the cell address where you need to click. The Calculated Items will be enabled if you click on either Row headers or Column Headers in the pivot table.
If you click in the values area, you won't see Calculated Items enabled.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Did it work for you?
vsuripeddiAuthor Commented:
Yes.. Thanks
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 Excel

From novice to tech pro — start learning today.