Pivot table sorting on Calculated field

[lwilt]

Hi,
I have a PT with 4 years of data in it. I'm creating calculated fields to show the difference in sales and % of difference in sales for 2017 and 2018. Those are the only two years I'm showing in the report with Year being a column value.

Because I want to see the difference in sales between 2017 and 2018 I've tried doing the calculated field on both previous year and 2017. Each time it works and also creates a blank column for 2017 to compare to.

I want to be able to sort by the calculated field but it always sorts the data by the blank 2017 field being created. I thought I could avoid the additional field being created by having the calculation being on 2017 instead of previous but that didn't work.

How can I sort my pivot table data by the calculated field that has data in it? Currently I'm going to more sort options>descending>selecting the calculated field but it always sorts by the blank column instead of the one with data in it.

I'm using Excel 2010.

Thanks for all the help!!

Sorry for accidentally posting it under excel dashboards, I meant to post it here. Please delete that thread.

 

[lwilt]

I also posted this question at:

https://www.excelforum.com/excel-ch...-sorting-on-calculated-field.html#post4961630

https://www.mrexcel.com/forum/excel...ble-sorting-calculated-field.html#post5129923

 

[lwilt]

I've attached a sample file to hopefully explain this better.

So in the PT I have two fields calculating the number of fruit sold per year with year being the only column value.

The 2nd fruit sold value is:
Show field value as difference from
Base field: year
Base item: previous

I'm trying to get the difference of what sold between 2018 and 2017. I've used base item as previous and 2017. Both work but each time it creates a blank column for 2017 to show it's calculation to the previous year.

I want to be able to sort the PT by the calculated field but no matter how I have the order of my columns under show field list it won't work. The sort always does it off the blank column made from the calculated field list when I try to use the calculated diff of what sold.