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!!

 

[lwilt]

I also posted this question at:

https://chandoo.org/forum/threads/pivot-table-sorting-on-calculated-field.39572/

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

 

[lwilt]

I've posted an example file on the other two links but will try to explain it better to help.

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.

[TABLE="width: 452"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2017[/TD]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Fruit sold[/TD]
[TD]Sum of F_Sold[/TD]
[TD]Fruit sold[/TD]
[TD]Sum of F_Sold[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]cherry[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]lemon[/TD]
[TD="align: right"]85[/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]-68[/TD]
[/TR]
[TR]
[TD]mango[/TD]
[TD="align: right"]46[/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD="align: right"]74[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]-69[/TD]
[/TR]
[TR]
[TD]pears[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]-7[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]288[/TD]
[TD][/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]-137[/TD]
[/TR]
</tbody>[/TABLE]