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 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!

 

 

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!

4 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    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
      Icon for Helper IV rankHelper IV
      8 years ago

      Hi Eric_Zhang

       

      It works pretty nice. Thank you very much. But I have another problem I don't know if it is related with the first one. When I select for example a filter like region or industry the Rank still shows me the overall rank but not the specific filter rank, any idea why it happens? It seems like it dosent recognize the filters applied.

    • 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!