Help with Rank function

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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!

[Anonymous]

Hi,

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

Thanks.

[Anonymous]

Hey Thanks @Anonymous 

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

[Anonymous]

Can you share some sample data?

Thanks.