0

If I have a pivot chart with a single axis, and three values columns (from two sheets), is it possible for me to combine two of these three values columns. In other words, in the below chart, can I combine the two New columns.

enter image description here

I've tried using Grouping, but that only applies to the axis, not the values. I tried swapping the x and y axis, but as they are still Values columns, I still couldn't group them.

Andrew Martin
  • 5,619
  • 10
  • 54
  • 92

1 Answers1

0

Add a Calculated Item (say Sum New) with Formula: 

='New - Created' +'New - Converted'

then filter out the columns not required for display.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • This looks like it could do what I want. I'm currently trying to find a way to make the Calculated Item field useable though. It's greyed out. A few google searches haven't helped yet. My data is all within the Excel sheet (no connections to outside). Any thoughts? – Andrew Martin Feb 17 '15 at 11:04
  • Yeah. I've selected one of the Row Labels, gone to Analyze - Fields, Items & Sets, but it's greyed out. All data is from within the worksheet and it's not in protected mode – Andrew Martin Feb 17 '15 at 11:07
  • Yes, I've tried selecting row and column labels. See my question edit. – Andrew Martin Feb 17 '15 at 11:10
  • Hmm. I can get a Calculated Item if I don't use the Data Model, i.e. if I have data from one sheet instead of two. Of course, doing this means I don't have all the data necessary to create the Calculated Item in the first place. – Andrew Martin Feb 17 '15 at 11:14
  • Got it. I can't seem to do this using a Data Model in Excel, but it can be done easily using PowerPivot (and [thanks to this question too](http://stackoverflow.com/questions/21901659/powerpivot-adding-calculated-item-to-a-field)). Thank you for your help. – Andrew Martin Feb 17 '15 at 11:20