Sorting an Excel Pivot Table by a calculated field

Question

I have the following Excel spreadsheet:

     A              B              C              D    
1   Product        Category         Sale        Margin
2   Product A       Apparel          500          45%
3   Product A       Apparel          400          30%
4   Product A       Shoes            600          55%
5   Product B       Apparel          300          20%
6   Product C       Beauty           100          40%
7   Product C       Shoes            200          65%
8   Product D       Apparel          450          25%
9   Product D       Beauty           700          50%
10  Product D       Beauty           250          35%

Based on this data I create a Pivot Table. In this Pivot table I added the following Calculated Field to get the profit:

   Profit = Sale * Margin

which leads to the following Pivot Table:

            Sum of Profit
Product A     1.950
Product B        60
Product C       315
Product D     1.540 

Now, I want to sort this Pivot Table descending so it looks like the following:

              Sum of Profit
Product A        1.950
Product D        1.540
Product C          315
Product B           60

It seems that for a Calculated Field the sorting options in the PivotTable-Tools is not available.

Do you have any other idea how I can achieve this sorting?

NOTE: I know that I could add the calculation of the profit in Column E in the source data. However, my original file is a bit more complex compared to the simplified example above so a Calculated Field is unavoidable.

Answer 1

Right click on a cell in the Calculated Field --> Sort --> Sort largest to smallest.

Or you can try the following code to sort the calculated field.

Sub SortCalculatedField()
Dim ws As Worksheet
Dim pt As PivotTable

Set ws = Sheets("Sheet1")   'This is the sheet which contains Pivot Table
Set pt = ws.PivotTables(1)
pt.PivotFields("Product").AutoSort xlDescending, "Sum of Profit", pt.PivotColumnAxis.PivotLines(1), 1
End Sub