How to Rank and ignore selections?

[tduarte]

Hi Community,

How to Rank and ignore selections if it's not possible to use set analysis in the Rank function?

I have an issue with the table below:

The Rank only ranks the values that have passed the data confidentiality rules in the IF condition.

Here's the Rank expression:

Aggr(
  Rank(If(Count({<[Reseller Size Band]=>} DISTINCT ResellerID)>=5
  AND Max({<[Reseller Size Band]=>}Aggr(Sum({<[Reseller Size Band]=>}Units),Country,Category,Vendor,[Reseller Size Band],[MMM-YY],ResellerID))/Sum({<[Reseller Size Band]=>}Units)<= 0.75
  AND Max({<[Reseller Size Band]=>}Aggr(Sum({<[Reseller Size Band]=>}Revenue),Country,Category,Vendor,[Reseller Size Band],[MMM-YY],ResellerID))/Sum({<[Reseller Size Band]=>}Revenue)<= 0.75
  ,-sum({<[Reseller Size Band]=>} Revenue)),4,1)
,Country,Category,Vendor,[Reseller Size Band],[MMM-YY])

I need the Rank to stay as is even after making a selection in one of the Bands.

I.e.: If I select >3m, it should should still show Rank=3 and not 1.

Any thoughts?

[#2]

Telmo,

Try this and adapt it to your model :

aggr(rank(sum({<Product=>} Amount), Product), Product)

aggr() is bound to the user state. Does not accept (directly) a set analysis

sum() accepts that set analysis

Fabrice

[tduarte]

Hi Fabrice,

Are you sure Rank can be done that way? Where does the Product fit in the function parameters?

rank( [ total ] expression [ , mode [, format ] ] )

[#4]

Yes, I am sure

Rank(expression, dimension)

where:

Expression = sum({set analysis} measure)

Dimension = product

Please, try it but adapt it to your model

Fabrice

[tduarte]

Ok, so I gave it a try and it kind of works but only if I have only one Vendor selected which is not an option. When there's more than one Vendor then it does not rank correctly.

My aggregation/rank needs be based on these 5 dimensions:

Country,Category,Vendor,[Reseller Size Band],[MMM-YY]

Any thoughts?

[tduarte]

In case this can't be done with Rank(), are there any other options to get the same result?

[#7]

Please try

AGGR(RANK(sum({1}[Net Sales])),[Branch Name],[Sales Operator])

[#8]

For several dimensions, I used this syntax and gave me the appropriate result (if I have understood your requirements):

aggr (rank(total sum({<Country=, Customer=, Product=>} Amount), Customer, Product), Customer, Product)

Fabrice

[#9]

[#10]

something like below not working ?

aggr(Rank(sum({<Product>}vol)),Product)