using rank function and variable simultaneously

[dreweezy]

Hi,

I need help with a syntax check. I have a table that has a column and measure. I've always got 4 buttons created using a variable to show me all the revenue for a given company. I'd ideally like to have my table generate and show me the top 10 revenue for a given company. 

Current syntax:

if($(vTop10) = 1, (sum({< Company = {"K-Mart"} >} [Items Revenue])),
if($(vTop10) = 2, sum({< Company = {"Wal-Mart"}>} [Items Revenue]),
if($(vTop10) = 3, sum({< Company = {"Best-Buy"}>} [Items Revenue]),
if($(vTop10) = 4, sum({< Company = {"Target"}>} [Items Revenue])))))

This works great. I am able to select any of the 4 buttons and it will give me the greatest sale goin from highest to low. I'd like to add a rank function so that now the table will only max have 10 highest values. I've tried the fixed limitation on the dimension but no luck since I'm working with a table. 

If(Rank([Items Revenue], 4) < 11, [Items Revenue])

This rank syntax works fine alone, but I am unsure on how to incorporate these 2 together with my given variable. Any help would be appreciated!

[dreweezy]

Sunny, this is exactly what I was looking for. My question has been answered. One thing I dont understand is I've done exactly what you have in the expression, placing the "-" in front of the sum as this seemed the only logical way to grab the bottom 5. I was getting some really off numbers. Not sure if this is a glitch or not, but regardless looks to be working now. I appreciate your help and thank you so much for taking your time helping me with this issue I had here!! 

[sunny_talwar]

You added them in two places... within Rank and true part of the if statement... but you needed it in just the rank part.

Also, you were just aggregating on product... where as you need to aggregate on country and product both

[dreweezy]

Oh ok I think I understand how that works now. Appreciate the help. Thank you very much.