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

[salfa_an]

Here is my dummy table

Product	Returned Sequence	Number	Price	Rank		wanted Rank
A	1	121	400€	1		1
A	2	150	0€
A	3	892	0€
A	4	563	0€
A	5	892	700€	5		2
A	5	892	1000€	5		2
A	5	892	100€	5		2
B	1	801	100€	1		1
B	1	801	5000€	1		1
B	2	653	0€
B	3	788	300€	3		2
B	3	788	0€

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 · Accepted solution]

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

[salfa_an]

Hi @OwenAuger 
i tried your first solution and it works. thank you so much