cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hcova Frequent Visitor
Frequent Visitor

Why Rank() has a discontinuity

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

 

Rankx() discontinuity.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
hcova Frequent Visitor
Frequent Visitor

Re: Why Rank() has a discontinuity

Thanks a lot.
You are right!!
Regards

View solution in original post

2 REPLIES 2
OwenAuger Super Contributor
Super Contributor

Re: Why Rank() has a discontinuity

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




hcova Frequent Visitor
Frequent Visitor

Re: Why Rank() has a discontinuity

Thanks a lot.
You are right!!
Regards

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors