Get top 10 from pivot table

[farrusete]

Hi

I have a Pivot table like this (named regionSales):

Region1
|------Advertiser1: Revenue1
|------Advertiser2: Revenue2
|...
|------Advertiser10: Revenue10
Region2
|------Advertiser1: Revenue1
|------Advertiser2: Revenue2
|...
|------Advertiser10: Revenue10
Region3
|------Advertiser1: Revenue1
|------Advertiser2: Revenue2
|...
|------Advertiser10: Revenue10
...
RegionN
|------Advertiser1: Revenue1
|------Advertiser2: Revenue2
|...
|------Advertiser10: Revenue10

And im creating a dashboard in another sheet in which i represent values for each region. I want to put there the Top10 advertisers for each region. How could I get advertiser name and revenue from that pivot which region name?

something like attached xls (note that data table has not a fixed height in rows (changes every day) and it is 50,000+ rows

thankyou in advance

 

[bobhc]

Good day farrusete

Have you used the top ten CF

 

[farrusete]

Hi bobhc

Thank you for your quick answer!

Sure it woud work but my data sheet will contain regions with less than 10 advertisers and i would like not to point data directly from pivot sheet (like "=pivot!A5" for top advertiser1 in region1 and "=IMPORTARDATOSDINAMICOS("amount";pivot!$A$3;"region";"r1";"advertiser";"Hyundai")" for that amount) because it would change if some of the regions has less advertisers or even no advertisers during the month.

Sorry if i didnt explain my issue in a better way. What i would need is to query the pivot table to know top advertiser names and revenues and not to point them manually from the dashboard every day.

Maybe i could add a new column to my data table in which calculate an "advertiser number" or something like that and then to get advertiser with"1" in that column and sum revenues with "regionX" but seems to be tricky.

Thank you again

 

[AIM]

@farrusete,

Select any advertiser on your pivot table and then Right click on it. From the options click "Filter", then click on "Top 10". In the Top 10 filter box make the selections "Show Top 10 Items by Sum of Amount".

You should now have Top 10 advertisers by Region.

 

[farrusete]

Thank you too AIM but im trying to find a less manual solution in order to get my dashboard tab updated automatically and not to have to point to cells manually every day

Again thank you for your reply

 

[AIM]

Hi farrusete,

Sorry for not being clear, but this is something that you would have to set once in your pivot tables. Once you have set the Top 10 filters, the Pivot tables would always show the Top 10 records whenever refreshed.

Hope this helps.

 

[farrusete]

No worries. im the one not being that clear

You can find attached what im trying to explain. If I use top10 filter, dashboard will be filled in that "manual way" (top.xlsx) but imagine that at the begining of february region2 has less than 10 advertisers (top2.xlsx). Then i will have to manually update dashboard sheet to point top advertisers from pivot sheet

Hope that this clarify my needs

Thank you again

 

[NARAYANK991]

Hi ,

Check your file here.

Narayan

 

[farrusete]

Thank you so mucho Narayan. It works!

Ill check those formulas to learn the way you did it

 

[aparvez007]

Hi ,

Check your file here.

Narayan

Awsome solution Narayan....