Why Rank() has a discontinuity

[hcova]

Hi,

Below I include a simple table called "Data" and a pivot table called "TD1".

I calculated the total P/L per Seller and I tried to rank it using MyRank measure based on Rankx() function.

Question : Can anybody explain me why using Rankx() doesnt show a continous ranking?

For instance, in the pivot table, under the MyRank column, US group, the  #5 ranking is not shown (lost).      In CAN group, #6 ranking is lost.!!!

Regards

[OwenAuger]

Hi @hcova

The problem in this case is that the table that RANKX is iterating over, namely ALL ( Data[Seller] ), includes all Sellers, even those that don't exist in the currently filtered Country.

The value of [Total P/L] for these these sellers is BLANK, which RANKX treats the same as zero. However you don't see these customers in the PivotTable because only existing combinations of Country/Seller are shown for columns from the same table.

In order to exclude these customers from the ranking, you could rewrite your measure like this, which will eliminate the "skipped" ranks:

Rank Fixed =
RANKX (
    CALCULATETABLE ( VALUES ( Data[Seller] ), ALL ( Data[Seller] ) ),
    [Total P/L],
    ,
    ASC,
    DENSE
)

Regards,

Owen

[hcova · Accepted solution]

Thanks a lot.
You are right!!
Regards