Pivot Table. Show Honey's Change in Ranking Position

[classic tzatziki]

I have data and created a pivot table.
Rows are the same....All food items.
Example used: Honey
I have two columns of data.
Column 1 = Period 1. Before
Column 2 - Period 2. After
I use rank function in pivot table to show the rank number from descender order (largest to smallest) within the respective columns.
Now, I want to show the difference in ranking position for a row item (let's call it Honey) if any (positive or negative, or none).
I want to see was there a change in position for Honey from period 1 Before to period 2 After.
Did the ranking position for Honey move and if so by how many spots? (i.e., up +1 spot, flat/0, down -4 spots)
How can I do that in the pivot table?
I would like to show numerical numbers with + and - signs attached.

 

[Chihiro]

I'd recommend that you upload sample workbook with desensitized data, and mockup of your desired output.

Pivot Tables and data model calculations are highly dependent on your data model and without looking at a sample, it is difficult to help.

 

[classic tzatziki]

I'd recommend that you upload sample workbook with desensitized data, and mockup of your desired output.

Pivot Tables and data model calculations are highly dependent on your data model and without looking at a sample, it is difficult to help.

Thank you for responding to my question. Sure, I'll fix up the data and upload to the thread.

 

[classic tzatziki]

I have data and created a pivot table.
Rows are the same....All food items.
Example used: Honey
I have two columns of data.
Column 1 = Period 1. Before
Column 2 - Period 2. After
I use rank function in pivot table to show the rank number from descender order (largest to smallest) within the respective columns.
Now, I want to show the difference in ranking position for a row item (let's call it Honey) if any (positive or negative, or none).
I want to see was there a change in position for Honey from period 1 Before to period 2 After.
Did the ranking position for Honey move and if so by how many spots? (i.e., up +1 spot, flat/0, down -4 spots)
How can I do that in the pivot table?
I would like to show numerical numbers with + and - signs attached.

Hello All, See attached the data with the desired output.

 

[Chihiro]

Ah, this is standard pivot table. Not PowerPivot...
This sort of calculation cannot be done in standard pivot table. It will require one of following to accomplish.
1. Add helper column to source data.
2. Create intermediate table and then base your pivot table on it.

Do you have access to Power Pivot?

 

[classic tzatziki]

Ah, this is standard pivot table. Not PowerPivot...
This sort of calculation cannot be done in standard pivot table. It will require one of following to accomplish.
1. Add helper column to source data.
2. Create intermediate table and then base your pivot table on it.

Do you have access to Power Pivot?

I do not have Power Pivot.

 

[Chihiro]

Then your option is either 1 or 2.

If raw data is structured like your sample, then go with option1.
Ex: =RANK.EQ(B2,$B$2:$B$21)-RANK.EQ(C2,$C$2:$C$21)

Add it to you pivot and apply custom number format to the field.
+#,##0;-#,##0;0

 

[classic tzatziki]

Then your option is either 1 or 2.

If raw data is structured like your sample, then go with option1.
Ex: =RANK.EQ(B2,$B$2:$B$21)-RANK.EQ(C2,$C$2:$C$21)

Add it to you pivot and apply custom number format to the field.
+#,##0;-#,##0;0

Thank you so much! I want to learn option 1 once I find out how to access Power Pivot. For now, I'll proceed with option 2.