Using Calculated Item in Excel Pivot Table

I am trying to use calculated item in excel to add together 2 items in a field.  the formula is very simple and works great:  =Committed+'Open Role' except for the fact it adds together every combination even when there aren't values for Committed and Open Roles.  This completely blows the pivot table up with a lot of blank rows.  Ideally I would want the calculated item to only calculate when either Committed or Open Role has a value.  Or is there a way to filter them out of the pivot table?
marku24Asked:
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.

byundtMechanical EngineerCommented:
The  behavior you describe is how the calculated field in a PivotTable is supposed to work. I avoid them for this reason.

As a workaround, I suggest using one of two alternatives:
1. Add the calculation formula to a column in your raw data. You may then add that field to your PivotTable as both a Values item and a Filters item. Use the Filter to hide those rows where your formula returns 0.
2. Create a formula outside the PivotTable that performs the calculation. Make sure you use cell addresses (e.g. D32) when building the formula. If you click on a cell to build the formula, you will get an unwanted reference to the GETPIVOTDATA function. Note that you will need to rebuild the formulas each time you Refresh your PivotTable.
0
marku24Author Commented:
I came across this link that seems to work for using calculated items.  I found the video for suppressing zero values helpful.

www.contextures.com/excelpivottablecalculateditem.htm
0
byundtMechanical EngineerCommented:
I suggest the Comment by marku24 as the Answer.
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
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
Pivot Tables

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.