Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
BI Consultant
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.
BI Consultant
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
Miguel this worked great, for what does the 3 represent and what does the 1 represent?
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
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)
)