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.

	Column Labels
	2017		2018
Row Labels	Fruit sold	Sum of F_Sold	Fruit sold	Sum of F_Sold
apple	36		56	20
cherry	28		30	2
lemon	85		17	-68
mango	46		31	-15
orange	74		5	-69
pears	19		12	-7
Grand Total	288		151	-137

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>