Forum Discussion

Moonc's avatar
Moonc
Frequent Visitor
3 years ago

RANKX Issues with 0s

I want my rank to start at 0 and go from there. Currently it is ranking my 0 values and the next lowest value as 1 and then ranking

=RANKX(FILTER(ALLSELECTED( [Program]), [NUMBER] ), [NUMBER],,ASC)

[Number] is a measure that is summing a value with some other conditions present

Expected Result

Program Number Rank

A1515
B2356
C1234
D01
E122
F553

Actual Result

Program Number Rank

A1515
B2356
C1234
D01
E121
F553

0 and the next lowest value are both getting ranked 1, which will not work for what I am trying to do.

 

I tried putting SUMX() around [NUMBER] in the ALLSELECTED, while that does work when the data is on its own, it breaks when I have to put the entire formula together (Two separate ranks for a matrix using ISINSCOPE() to change which rank is being applied)

 

I also tried setting the rank of 0 to 1 and then ranking +1. While this works for instances that there is one 0 present, if there are multiple 0s, I need the rank to skip. So both the 0s would be ranked 1 and then the next lowest value would be ranked 2, when I need it to be rank 3.

 

Any idea? I feel like I am not understanding something as the rank should just be applied to how the values are presented...

4 Replies

    • Moonc's avatar
      Moonc
      Frequent Visitor
      3 years ago

      FreemanZ That works for when I am not working in a matrix. I should have made this more clear. I have a matrix with both program and subprogram, and I need to have a rank for each. So the formula that I am actullay using looks like the below

       

      RANK = 

      VAR _program = RANKX(ALLSELECTED( [Program]), [NUMBER],,ASC)

      VAR _subProgram = RANKX(ALLSELECTED( [subProgram]), [NUMBER],,ASC)

       

      RETURN

      if(isinscope(program),_program,if(isinscope(subProgram),_subProgram))

       

      This returns a proper rank for the program, but the subProgram has all the same rank

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        3 years ago

        Moonc , if both are on row and program is first and sub program is second then try like

         

        RANK =

        VAR _program = RANKX(ALLSELECTED( [Program]), [NUMBER],,ASC)

        VAR _subProgram = RANKX(ALLSELECTED( [subProgram]), [NUMBER],,ASC)

        Switch (True()

        isinscope(subProgram,_subProgram,

        isinscope(program),_program

        )

         

        The first one or the parent is always isinscope for the child 

         

        IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM