cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SaiPranav Occasional Visitor
Occasional Visitor

RANKX is not ranking negative numbers

I'm new to Power Bi Desktop. I'm trying to rank a text variable with respect to a decimal variable. I used RANKX function for this. Its working even for filters as well. But I'm getting problems  when decimal variable becomes negative. Its working fine for positive decimals. For negative decimals the rank is showing as a different number from the rank that is shows for zero. Ideally it should be increased by 1. Then again after showing some random number for -0.01 the rank is increaing for -0.02 and is working perfectly fine. 

 

For positive decimals set ranking is fine and for negative decimal set ranking is good. Its just that the ranks dont match for both of them, Ideally rankings should just increase as we go from positive to negative.

Need help! Thanks in advance.

6 REPLIES 6
Super User
Super User

Re: RANKX is not ranking negative numbers

Can you provide a concrete example of what you are referring to? I have read your post three times and I am still confused. Some example data and your results would be great.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Sean Super Contributor
Super Contributor

Re: RANKX is not ranking negative numbers

RANKX works with negative numbers - what measure have you written?

Moderator v-caliao-msft
Moderator

Re: RANKX is not ranking negative numbers

Hi SaiPranav,

 

Please provide us some sample data, so that we can try to reproduce this issue and make further analysis.

 

I have tested it, RANKX function works fine for negative numbers.

Create two measures.
TotalValue = SUM('RankX'[Value])
RankXValue = RANKX(ALL('RankX'[ID]),[TotalValue],,,Dense)

Capture.PNG

 

Regards,

Kirill_Moscow Frequent Visitor
Frequent Visitor

Re: RANKX is not ranking negative numbers

It seems than the problem is when in ranking you use measure that can return negative value. Here small example:

Table: "Shops"

Shop     |   Value     |  Territory

Shop1   |     10         |    A

Shop2   |     5           |    B

Shop3   |     2           |    B

Shop4   |    -4          |    A

 

Two measures:

Measure = MAX('Shops'[Value])

Rank = RANKX(ALLEXCEPT('Shops';Shops[Territory]);'Shops'[Measure])

 

When you set a filter on territory "A" you get wrong result:

 

Territory  |   Shop    |   Value     |  Rank

   A          |  Shop1   |     10        |    1

   A          |  Shop4   |     -4        |    4  - instead of 2 !

 

For territory B everything is correct

 

Territory  |   Shop    |   Value     |  Rank

   B          |  Shop2   |     5        |    1

   B          |  Shop3   |     2        |    2   

Kirill_Moscow Frequent Visitor
Frequent Visitor

Re: RANKX is not ranking negative numbers

It seems than the problem is when in ranking you use measure that can return negative value. Here small example:
Table: "Shops"
Shop      |   Value   |   Territory
Shop1    |    10       |   A
Shop2    |    5         |   B
Shop3    |    2         |   B
Shop4    |   -4         |   A

Two measures:
Measure = MAX('Shops'[Value])
Rank = RANKX(ALLEXCEPT('Shops';Shops[Territory]);'Shops'[Measure])

When you set a filter on territory "A" you get wrong result:

Territory |  Shop     |   Value    |   Rank
   A         |   Shop1  |    10        |    1
   A         |   Shop4  |     -4       |    4 - instead of 2 !

For territory B everything is correct

Territory  |    Shop    |   Value   |   Rank
       B      |    Shop2  |       5       |    1
       B       |   Shop3  |       2       |    2

abdoesmat Frequent Visitor
Frequent Visitor

Re: RANKX is not ranking negative numbers

firstdHigh = VAR customer=SELECTEDVALUE(V_HH_VisitDuration[CustomerNo])
return
CALCULATE(SUM(V_HH_VisitDuration[visit duration]);FILTER(ALL(V_HH_VisitDuration);V_HH_VisitDuration[CustomerNo]=customer && [RANKING]=1))   

in this dax how to get the rank value thats represents the min value ?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 116 members 1,632 guests
Please welcome our newest community members: