Forum Discussion

trishd1223's avatar
trishd1223
Frequent Visitor
3 years ago
Solved

Rankx skipping 0's/blanks

Hi - I'm having trouble with Rankx.  I don't want to rank 0's or blanks.  I was able to get the rankx to avoid assigning a rank # to 0's, however, it doesn't skip and resume to the next rank #.  For example, the -$73k I want to be a rank of 4 instead of 2809. 
Here is the formula I used to get these results. 
 
Rank adam test $ chg 2 = IF([$ Chg Column for Ranking - adam]<>0, IF(ISINSCOPE((AppendedData[Brand])), RANKX(ALLSELECTED(AppendedData[Brand]),[$ Chg Column for Ranking - adam])))
 
Thanks! 
Need Help
  • Fowmy's avatar
    Fowmy
    3 years ago

    trishd1223 

    Can you try the following measure?

    Rank adam test $ chg 2 =
IF (
    [$ Chg Column for Ranking - adam] <> 0
        && ISINSCOPE ( AppendedData[Brand] ),
    RANKX (
        FILTER (
            ALLSELECTED ( AppendedData[Brand] ),
            [$ Chg Column for Ranking - adam] <> 0
        ),
        [$ Chg Column for Ranking - adam]
    )
)

2 Replies

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

    trishd1223 

    Can you try the following measure?

    Rank adam test $ chg 2 =
IF (
    [$ Chg Column for Ranking - adam] <> 0
        && ISINSCOPE ( AppendedData[Brand] ),
    RANKX (
        FILTER (
            ALLSELECTED ( AppendedData[Brand] ),
            [$ Chg Column for Ranking - adam] <> 0
        ),
        [$ Chg Column for Ranking - adam]
    )
)
  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Try this: 
    Rank adam test $ chg 2 =     IF([$ Chg Column for Ranking - adam]<>0, IF(ISINSCOPE((AppendedData[Brand])), RANKX(FILTER(ALLSELECTED(AppendedData[Brand]) && [$ Chg Column for Ranking - adam] <>0 && [$ Chg Column for Ranking - adam] <>BLANK() ,[$ Chg Column for Ranking - adam])))