Aggr(Rank function help needed:Top 10 "for each Brand" wanted Not Total top 10 in Pivot Table

[#1]

Two Brands:

Coke

Pepsi

Each Brand have multiple flavors

I have the below aggr(rank formula that gives me Total Top 10 Customer sales

but I want Top Ten Customer Sales for Each brand, "Not Total combined top 10"

=IF(

aggr(

rank(total

aggr(sum([2011 Sales]),Flavor)

,0,1)

,Flavor)

<=10,

  aggr(

rank(total

aggr(sum([2011 Sales]),Flavor)

,0,1)

,Flavor))

If i select a Brand Coke or Pepsi then my Top 10 pivot table recalcuates the Top 10 for the Brand selection (which is what i want to show by default and not have to manually select a brand for a Top 10 by brand to be calculated)..

I am trying to display Top 10 for each brand by default, and not have to manually select brand for the Top10 to recalculate.

The attached example is version 10, once opened you will have to turn Web view off,since it is set up to open in webview upon opening.

Once opened, Click View, Turn On/Off Webview

attached is an example:

[Miguel_Angel_Baeyens · Accepted solution]

Hi Joseph,

Try the following calculated dimension instead, and check the "Suppress When Value Is Null" to avoid results that are not in the top 10 showed with null "No"

=If(Aggr(Rank(Aggr(Sum([2011 Sales]), Brand, Flavor), 3, 1), Brand, Flavor) <= 10,
Aggr(Rank(Aggr(Sum([2011 Sales]), Brand, Flavor), 3, 1), Brand, Flavor)
)

Hope that was what you were looking for.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Miguel,

I knew i was on the right track but am very new to Aggr and Rank funtions.  Thank you for your help.

This worked perfectly.  I know that  Top customers, sales, etc come up a lot with in pivot tables so hopefully this can help someone else too.

Kind regards,

Joseph

[#4]

Miguel this worked great, for what does the 3 represent and what does the 1 represent?

[#5]

It's parameters  from the rank function to treat  the equal item.

How to calculate all the equal ?

How to  display ?

Look at the help.

JJ

[Anonymous]

Thanks so much for this. It has helped me so much.

I know this post is old, so not sure if you can still help me.

I want to use set analysis to exclude selections, but it doesn't seem to work in the calculated dimension for the rank.

I just added the {1} but it only brings back whatever my selections are. Is there something else I should be doing?

=If(Aggr(Rank(Aggr(Sum({1}[2011 Sales]), Brand, Flavor), 3, 1), Brand, Flavor) <= 10, 

Aggr(Rank(Aggr(Sum({1}[2011 Sales]), Brand, Flavor), 3, 1), Brand, Flavor) 

)