Forum Discussion

salfa_an's avatar
salfa_an
New Member
3 years ago
Solved

How do I rank values within groups ignoring blanks/zero according to its returned sequence?

Here is my dummy table

ProductReturned SequenceNumberPriceRank wanted Rank
A1121400€1 1
A21500€   
A38920€   
A45630€   
A5892700€5 2
A58921000€5 2
A5892100€5 2
B1801100€1 1
B18015000€1 1
B26530€   
B3788300€3 2
B37880€   

 

I calculated the Rank Column with:

 

 

Rank = IF(Table1[Cost] <> 0, RANKX(FILTER(Table1, Table1[Product] = EARLIER(Table1[Product])), Table1[Returned Sequence],, ASC, Dense))


Is there any other formula/workaround to get the rank like in the wanted rank column? Thank you!

  • OwenAuger's avatar
    OwenAuger
    3 years ago

    Hi salfa_an 

    Here are a couple of options:

     

    1. Modify your original expression to ensure Cost <> 0 within FILTER:

    Rank 2 =
IF (
    Table1[Cost] <> 0,
    RANKX (
        FILTER (
            Table1,
            Table1[Product]
                = EARLIER ( Table1[Product] )
                && Table1[Cost] <> 0
        ),
        Table1[Returned Sequence],
        ,
        ASC,
        DENSE
    )
)

     2. Rank over a table of distinct values of Returned Sequence, created using CALCULATETABLE:

    Rank 3 = 
IF (
    Table1[Cost] <> 0,
    VAR RankingTable =
        CALCULATETABLE (
            VALUES ( Table1[Returned Sequence] ),
            ALLEXCEPT ( Table1, Table1[Product] ),
            Table1[Cost] <> 0
        )
    RETURN
        RANKX (
            RankingTable,
            Table1[Returned Sequence],
            ,
            ASC
        )
    )

     

    Do these work for you?

    Regards

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    3 years ago

    Hi salfa_an 

    Here are a couple of options:

     

    1. Modify your original expression to ensure Cost <> 0 within FILTER:

    Rank 2 =
IF (
    Table1[Cost] <> 0,
    RANKX (
        FILTER (
            Table1,
            Table1[Product]
                = EARLIER ( Table1[Product] )
                && Table1[Cost] <> 0
        ),
        Table1[Returned Sequence],
        ,
        ASC,
        DENSE
    )
)

     2. Rank over a table of distinct values of Returned Sequence, created using CALCULATETABLE:

    Rank 3 = 
IF (
    Table1[Cost] <> 0,
    VAR RankingTable =
        CALCULATETABLE (
            VALUES ( Table1[Returned Sequence] ),
            ALLEXCEPT ( Table1, Table1[Product] ),
            Table1[Cost] <> 0
        )
    RETURN
        RANKX (
            RankingTable,
            Table1[Returned Sequence],
            ,
            ASC
        )
    )

     

    Do these work for you?

    Regards