Top 10 list in Pivot based on 2 fields

[sidheshmangle]

Hello all, I am not sure - If similar request has been posted on this forum.


I got data of 4 fields - 1) Month, 2) Plant 3) Material & 4) Variance value (Calculated using Pivot formula = Actual / Scheduled)


Pivot has the option to filter the Top 10 value on field 'Variance value'

But it shows only for 1 dimension (Either Month or Plant) & not both.


1 plant has > 100 materials & there are total 60+ plants


What i need is -> For each Plant - Pivot should give me Top 10 Materials which has maximum 'Variance value'


Let me know if there is any solution to this - To view top 10 values on 2 fields (Plant & Material as a group)

 

[Luke M]

Just to clarify, are you wanting each plant to show the same 10 materials, or should they each show their Top 10 for that plant?

If the latter, you can right click on the Material Field and filter it to show Top 10 based on Variance. If the former, you need to reverse the order of your Plant and Material fields.

 

[sidheshmangle]

Hello Luke, To clarify - What i am looking for, I've uploaded the file on below link


http://www.speedyshare.com/rtzxu/Top10-Filter-Report.xlsx


1st tab 'Dashboard' - User will select Plant & Month from dropdown listbox &

then materials with highest variance% needs to be shown in the table as highlighted in yellow. Data tab has all the required data.


I guess - Index/offset may work, But its been long time i hvn't used them


2nd tab - Pivot is as per - Plant -> Materials -> Variance%

I want the pivot to be filtered by Top10 Materials with highest variance% for each plant


i.e Each Plant will show Top10 materials with highest variance%


Let me know your thoughts on this.. Thnx much in advance