topic Re: Help with Rank function in DAX Commands and Tips

Help with Rank function

Anonymous — Thu, 05 Sep 2019 06:36:36 GMT

Hello,

I am trying to apply RANKX function to show bottom 5 records based on amount which I am able to achieve.

Next I want is it should filter my data based on specific condition and then apply rankx fubction.But I am not able to achieve desired result. Need help with DAX to implement the same,

e.g: When I apply normal Rank function to show bottom 5 product type by Amount then I get results(A,B,C,D,E)

I want dax to show bottom 5 product type by product amount where transaction no >1 i.e (B,C,D,E,F)

Product Type Product Amount No of Transaction Rank Rank( Excluding transaction where no is >1)

A 10 1 1

B 20 12 2 1

C 30 30 3 2

D 40 45 4 3

E 50 15 5 4

F 60 60 5

Thanks in advance.

Re: Help with Rank function

Anonymous — Thu, 05 Sep 2019 07:20:11 GMT

Hi,

Create 2 measures as below and drag 'Product Rank' measure in the table:

Total Amount = SUM( Data[ProductAmount] )

Product Rank = 
IF( SUM( Data[NoOfTransactions] ) <> 1,
RANKX( ALL( Data ), [Total Amount], ,ASC ) - 1, "No Rank"
)

Here's what i got:

Thanks.

Re: Help with Rank function

Anonymous — Thu, 05 Sep 2019 07:45:05 GMT

Hi Vaibhav,

Thanks ..But some how I am not getting expected results.

DAX i m using :

Bottom Rank = if(sum(Query2[NUMBER_TRANSACTIONS])<>1,RANKX(ALL(Query2[DESCRIPTION]),[Amount],,ASC)-1,"No Rank"

Am I doing anything wrong?...my rank is not starting with 1.

Kindly suggest.

Thanks!

Re: Help with Rank function

Anonymous — Thu, 05 Sep 2019 07:49:29 GMT

Hi,

Can you try to replace ALL(Query2[DESCRIPTION]) with ALL(Query2)

Thanks.

Re: Help with Rank function

Anonymous — Thu, 05 Sep 2019 10:07:12 GMT

Hey Thanks Anonymous

Tried with ALL(Query) still not able to get the correct rank..Please see below.

Re: Help with Rank function

Anonymous — Thu, 05 Sep 2019 13:11:15 GMT

Can you share some sample data?

Thanks.