Pivot Tables - Calculated Field Help

Here is my question. If my data has one column called Scenario (Current Year, Prior Year, Budget), and a couple measurement columns for volume and revenue, how do I make a calculated field to compare my Current Year measure against my Prior Year and Budget?

When I try to make a calculated field (Current Year v. Prior), how do I tell the field to pick volume for only the scenario of Current Year, and subtract volume from only the scenario of Prior Year?

Thanks!

Keith

Calculated Field.png

OK, here is the sample file. My calculations are off to the right, but I would like to be able to make those a calculated field in the pivot table. My problem is that I don't know how to enter the formula for my calculated field to say something like "VOLUME (When Scenario = Current Year) - VOLUME (When Scenario = Prior Year)."

Keith

Hi,

I could do this by modifying the data layout, see the attached file.