Filter Top N "Calculated" Values in Pivot Table

[mamady]

Hi Dears,

This is pushing me crazy, sounds so simple but it is not working out!

I want to filter the top N calculated values in a pivot table, but it keeps filtering the top data point values!

For example, below is my pivot table

added the "Amount" Field again to the Values section >> Value Field Settings >> Show Values as (Difference from) (previous) "Month"
Then, I disabled the automatic sort in order to be able to sort the newly calculated value which I named "Delta", largest to smallest

Now, the issue is when I try to filter top "Delta"records. For example if I want to filter top 5, I am following the below steps

it resulted in

which is not the top 5 "Delta", rather it is the top Total Amount of Jun-20!!

any idea what I am doing wrong?

Thank you in advance

 

[ER_Neha]

Hello, I tried working with your data. And its working fine. In the pivot table---
1. "item" will be in rows
2. "may", "june","delta" data will be in values in this respective order.

Book1
	A	B	C	D	E	F	G	H	I
1	items	may	june	delta		items	Sum of may	Sum of june	Sum of delta
2	matt	244	479	235		apple	172	277	105
3	tv	233	368	135		car	352	468	116
4	car	352	468	116		matt	244	479	235
5	apple	172	277	105		pen	418	435	17
6	pen	418	435	17		tv	233	368	135
7	rat	297	292	-5		Grand Total	1419	2027	608
8	skull	335	321	-14
9	cat	473	423	-50
10	book	412	321	-91
11	scale	299	193	-106
12	phone	258	148	-110
13	scar	353	236	-117
14	skin	428	232	-196
15	dog	459	259	-200
Sheet1

 

[mamady]

Hi Dear,

Thanks for taking the time

You created another Pivot table where the delta is already a field. I have no issues with this approach, by my data set is different and contain more than just two months as this was just an example.
In my example, the original table contains only May and June in tabular form, then the pivot is inserted and the delta is created using " Show Values as" function.

you may check the sample sheet on the following link

Top N example.xlsx

drive.google.com

Best Regards

 

[ER_Neha]

Hello. Actually I didn't get your question properly at the first instance. but your next reply made me understand a bit the requirement of your output.

So, in pivot table I didn't find the solution using show value as, but in pivot table analyze ribbon tab there is an option on the right side stating "fields, items & sets", upon clicking that you will get an option of the calculated field, hit it, then name the field "delta" and write the formula by selecting "June" field then a minus sign"-" then insert "may", lastly click add. the table will automatically generate your field, and then you can sort the table for obtaining the top numbers according to the delta.

 

[mamady]

Thank you