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/pi...d-field.39572/

https://www.mrexcel.com/forum/excel-...ml#post5129923

[JeteMc]

Not sure that I understand why a blank field is created and used for sorting. Perhaps a small desensitized sample of the data and pivot table showing what you have and, manually, what you want would help.
To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Also this article may also be of some help, although it may be describing the process that you are already using.
Let us know if you have any questions.

[lwilt]

I've attached an example file.

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.

[rorya]

You could add two columns to the source data to return values for 2017 and 2018, then add a calculated field that subtracts one from the other. You can then sort by that field.

[lwilt]

You could add two columns to the source data to return values for 2017 and 2018, then add a calculated field that subtracts one from the other. You can then sort by that field.

All my sales data is in one field so that's why I'm using year as a column to split the sales that way.

Is there a way you can do what you're suggesting with my sample data but keeping the data in the same fields?

[rorya]

If you have Power Pivot and/or Power Query, yes. If not, no, not without the additional columns.

[JeteMc]

Perhaps this will help.
On sheet1 the 'Diff' column is populated using:

Please Login or Register  to view this content.

In the Pivot table on sheet3 the Type of fruit is sorted (More Sort Options) by the Sum of Diff column (2018).
Let us know if you have any questions.

[lwilt]

If you have Power Pivot and/or Power Query, yes. If not, no, not without the additional columns.

I've looked into doing the report as a Power Pivot in the past but the issue I always ran into was our CRM splits sales data into a number of tables and I couldn't find a way to append within the PP to get all the sales.

It appeared to me it's easy to map tables together to get data out but there wasn't a way to append on top of the table. Is there a way around that?

[lwilt]

Perhaps this will help.
On sheet1 the 'Diff' column is populated using:

Please Login or Register  to view this content.

In the Pivot table on sheet3 the Type of fruit is sorted (More Sort Options) by the Sum of Diff column (2018).
Let us know if you have any questions.

I'm not really familiar with the agg function...how does it know to reference column B year when doing the difference? In my real file I would want to do it by both year and month depending on what the consumer is wanting to filter by.

[rorya]

If you have Power Query, appending tables is a doddle.

[lwilt]

If you have Power Query, appending tables is a doddle.

I'm on excel 2016. I don't see where I can get to power query. I went into get data but don't see it listed in any of the drop downs.

[rorya]

If you have 2016, then you have it. It's called Get and Transform and is on the Data tab.

[JeteMc]

Responding to post #10. The aggregate function, as used in this proposed solution, is a way to find the largest value that meets the criteria, without using a LARGE(IF... array entered formula (since your profile shows you are using Excel version 2013).
The formula doesn't concern itself with the year as in the sample data each fruit is only listed once per year and years are grouped in ascending order. If that isn't representative of your actual data then I would suggest uploading a sample that is. Also please manually mock up some examples of the way the consumer may wish to view output as in difference with previous month and/or same month of the previous year.
Let us know if you have any questions.

[lwilt]

If you have 2016, then you have it. It's called Get and Transform and is on the Data tab.

I've used Microsoft query and SQL query in that section but never have seen a way to append...how can that be done?

[rorya]

New query - From file - From Excel workbook. Repeat for each file. (If they are all in the same folder, you can use the From Folder option, which is even easier if they have the same structure) Then you can create an Append query in the query editor to amalgamate them all.