RANKX Issues with 0s

[Moonc]

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

A	151	5
B	235	6
C	123	4
D	0	1
E	12	2
F	55	3

Actual Result

Program Number Rank

A	151	5
B	235	6
C	123	4
D	0	1
E	12	1
F	55	3

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

[FreemanZ]

hi @Moonc 

how about:

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

[Moonc]

@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]

@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

[Moonc]

@amitchandak This does not work, I am seeing the program ranks correct, but the subprogram ranks are incorrect. I don't see any pattern in the rank that would help me understand.