% of difference

[GAHOOTENBOB]

OK I am new to pivot tables and I am having difficulty getting the % of difference to work for me.

I have a field "units authorized" and another field "units served". All I want to do essentially is units served/units authorized. So I added a second field of Units Served to the table, named it "percent of units served", I selected a cell in that column and in the value fields settings I chose summarize by sum and in the show values as I chose % difference and selected units authorized for base field and I have tried selecting previous, next and the first value listed for the base item. I have been unable to get it to work so far and it always returns #N/A.


What am I doing wrong?

 

[Dan Waterloo]

I think you need what is called a "calculated field". You can read about it in the Excel Help file under "About calculations and formulas in PivotTable and PivotChart reports."

 

[GAHOOTENBOB]

Thanks Dan. I'll check that out.

I am curious...To use the % difference option do you have to be comparing the same field - like units served for 2007 vs 2008? and not comparing 2 different fields like I am trying to do?

 

[Dan Waterloo]

Yes, % of difference is used to compare the values of a single data field of all of the other categories against a chosen category. So if you chose 2007 as your base category it would show 2006, 2008, and 2009 in terms of how they compared to 2007.

 

[GAHOOTENBOB]

Thanks Dan!