Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
8 years ago
Solved

RANKX issue with some measures

Hi!   I have a problem with RANKX, for one measure works but for others dosent.   I have this table with multiple measures and I need a rank for some ones like this:  I did a rank measure ...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago
    AlejandroPCar wrote:

    Hi!

     

    I have a problem with RANKX, for one measure works but for others dosent.

     

    I have this table with multiple measures and I need a rank for some ones like this:

     I did a rank measure called "Rank Valores" for evaluate the "Valores" column and it works pretty nice. But when I tried to do the same with "Rank Dif I / U" for evalute the "Dif U / I " column it seems like have some troubles with negative values. Is there a way to solve that? Here is my pbix file if it will be necesary: https://1drv.ms/u/s!AtTnrgPUQzQCjX7-2mlyPJQkQX4a

    Thanks a lot for your help!

     

     

    AlejandroPCar

    It is a context issue. Try

    Rank Dif U/I =
VAR Ran =
    RANKX (
        ALLEXCEPT ( Homogenea, Homogenea[nit] ),
        CALCULATE ( [Dif U / I], ALLEXCEPT ( Homogenea, Homogenea[empresa] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( [ERH V] = 0, BLANK (), Ran )

  • AlejandroPCar's avatar
    AlejandroPCar
    8 years ago

    Hi!,

     

    I actually solve the problem, but there is a strange secondary effect like this:


     Why the rank ignores the number when it pass from positive to negative? In this case is from 9 to 11, but where is the 10? The formula is

     

     

    Rank Dif U/I =
    VAR Ranking =
        RANKX ( ALL(  Homogenea[empresa] ); Homogenea[Dif U / I] ; ; 0 ; Dense )
    RETURN
        IF ( [ERH V] = 0 ; BLANK () ; Ranking )

     

    But, like it seems my problem is solved, thank you, this "issue" is really a issue? or this may not affect the result. Thanks!

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
8 years ago

Hi!,

 

I actually solve the problem, but there is a strange secondary effect like this:


 Why the rank ignores the number when it pass from positive to negative? In this case is from 9 to 11, but where is the 10? The formula is

 

 

Rank Dif U/I =
VAR Ranking =
    RANKX ( ALL(  Homogenea[empresa] ); Homogenea[Dif U / I] ; ; 0 ; Dense )
RETURN
    IF ( [ERH V] = 0 ; BLANK () ; Ranking )

 

But, like it seems my problem is solved, thank you, this "issue" is really a issue? or this may not affect the result. Thanks!

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi,

 

I'm encoutering the same problem once the RANKX() function switches from positive to negative numbers. I see a gap in the ranked values [testVar]. In my case it goes from 239 to 243. There are 4 blanks inbetween. See below:

Scenario	Scen Pnl	testVar
20180409F	-38500	236
20180110F	-14988	237
20180827F	-11127	238
20171025F	-9365	239
20170915F	11511	243
20181214F	52688	244
20180806F	54522	245
20180118F	55117	246
20190308F	66220	247

I am using a following formula where I explicitly skip the RANKX() for the records where there is no [Scen Pnl] value, yet what the RANKX() does it just "hiding" those records in the table view, but taking them into account in the rank.

 

testVar = 
IF(
    NOT(ISBLANK([Scen Pnl])),
    RANKX(
        ALL(Scenario[Scenario]),
        [Scen Pnl],
        ,
        ASC,
        Skip
    ),
    BLANK()
)

 Is there any way to force RANKX() function to only calculate the RANKX() value for NON BLANK values in the second argument [Scen Pnl]?

 

Many thanks for your help in advance!