Filter Top N "Calculated" Values in Pivot Table

mamady

Board Regular
Joined
Sep 23, 2011
Messages
230
Office Version
  1. 365
Platform
  1. Windows
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
1596433966488.png


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

1596435059276.png


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

1596435159976.png


1596435190924.png


it resulted in
1596435231296.png


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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1itemsmayjunedeltaitemsSum of maySum of juneSum of delta
2matt244479235apple172277105
3tv233368135car 352468116
4car 352468116matt244479235
5apple172277105pen41843517
6pen41843517tv233368135
7rat297292-5Grand Total14192027608
8skull335321-14
9cat473423-50
10book412321-91
11scale299193-106
12phone258148-110
13scar353236-117
14skin428232-196
15dog459259-200
Sheet1
Untitled.jpg
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
230
Office Version
  1. 365
Platform
  1. Windows
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

Best Regards
 

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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.
Untitled.jpg
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top