Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SaiPranav
New Member

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.

8 REPLIES 8
Anonymous
Not applicable

I ******* solved it at last. The problem is that when you filter, your measure returns "0" values, which negative numbers count after. What you need to do is to apply a formula like to ignore blanks in your filter column like: Filter(Allselected('Tablename'[Columnname]),[Measure]<>blank()) Let me know if this works for you guys.

Thank you!

abdoesmat
Regular Visitor

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 ?

v-caliao-msft
Employee
Employee

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,

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

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   

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.