Help with Rank Function in Set Analysis

[Anonymous]

 Hi Experts,

I'm working on a retail dashboard and trying to create a table showing top 10 product sales, top 11-100 product sales, top 101-200 product sales, etc.

I figured out the formula for top 10 sales:

Sum({<[Article Code] = {'=Rank(sum(Net Sales))<=10'}>}[Net Sales]) 

But when I tried to change the formula to calculate top 11-100 product sales, I couldn't figure out the right syntax for putting in the range. Any advice would be greatly appreciated!

Thanks,

Shirley

[vikramv]

Can you try with using double quotes inside the set analysis like this ?

Sum({<[Article Code] = {"$(=Rank(sum(Net Sales))<=10)"}>}[Net Sales]) 

[Anonymous]

The formula works with the single quotation mark. It gives the right result. The challenge I have is to change this part [Article Code] = {"$(=Rank(sum(Net Sales))<=10)"}into a range, for example instead of top 10 products, I want top 10-100 products.

[vikramv]

Then can you use , Rank(sum(Net Sales))<=10 on dimension with Aggr like 

IF ( Aggr( Rank(sum(Net Sales)),Dim1 , Dim2 .....) <=10 , Dimension ) with unchecking the  show null / zero values ?

 On the expression just keep the sum(Net Sales)

[Anonymous]

Hi Vik, maybe I was not clear. What I'm looking for is to get the sales for products with sales ranked from 10 to 100, so ideally something like :

Sum({<[Article Code] = {"$(=Rank(sum(Net Sales))>=10<=100)"}>}[Net Sales])

But I'm not sure what the right syntax is. I don't see the need for an aggregation function, as all I want is a total sales number.

[vikramv]

Sorry I didn't observed the "[Article Code]" in quick.

Please try like this .

Use the IF ( Aggr( Rank(sum([Net Sales])),[Article Code] ) >=10 and Aggr( Rank(sum([Net Sales])),[Article Code] )<=100 , [Article Code] ) as dimension

Use sum([Net Sales] as measure.

 For testing if it works , You can cross check by adding "Aggr( Rank(sum([Net Sales])),[Article Code] ) " as additional dimension and change it to straight table.

[Anonymous]

I tried the formula and it didn't work 😞
I guess the problem with this formula is that the field "Article Code" is not numeric, so that we cannot put the range around [Article Code]

[vikramv]

Make another try like this , Use a Straight Table

Dimension 1 : [Article Code]

Dimension 2 : Aggr( Rank(sum([Net Sales])),[Article Code] )

Expression  :  sum([Net Sales]

Create a copy of this chart and change the Dimension 2 as follows

Dimension 1 : [Article Code]

Dimension 2 :  IF ( Aggr( Rank(sum([Net Sales])),[Article Code] ) >=10 and Aggr( Rank(sum([Net Sales])),[Article Code] )<=100 , [Article Code] ) 

Expression  :  sum([Net Sales]

Compare the values , you would come to know the issue.