Pivot Table - % of Difference - N/A

[mccallum]

I have a pivot table which displays the Year/Qtr and Foreclosure (Y/N) under Row Label.

I then have Avg List Price, Avg Sold Price and Avg DOM under the Values.

I thought if I added the Sold Price field again, clicked on Value Field Settings - Show Value As -- I would be able to choose % Difference From and List Price and the result would be a field which showed the percentage difference between the Avg List Price and the Avg Sold Price. But all I get is N/A. I have created another field on the spreadsheet to accomplish the same formula but shouldn't this work from the Pivot Table Field List?

Also, I would like to be able to show the affect the foreclosures are having on the market during each quarter. Is it possible to do the same thing as the % Difference From if the Quarter is not drilled down? Currently I have created a formula to add the foreclosure number to the non-foreclosure number and then divide the foreclosure number by the total. This works but if I take away the drill down the formula shows an error.

Thanks!

 

[XL&ME]

To get you started, I think you will need to look at calculated fields in pivot tables. This will allow you to do math on the pivot table .