RANKX to skip blanks - calculated column

[bsz412]

Hi, 

I have the following calculated column

Rank = 

RANKX (

CALCULATETABLE (

EPD,

ALLEXCEPT ( EPD,EPD[Country],globalproduct[ETIM_class] )

),

EPD[AVG total per ETIM and country],

,

ASC,

DENSE

))

I have a few cells were EDP[AVG total per ETIM and country] is blank, and I would like RANKX to skip those when calculating rank. 

I have tried this: 

Ranking2 =

IF(

NOT (ISBLANK (

EPD[AVG total per ETIM and country]

)),

RANKX (

CALCULATETABLE (

EPD,

ALLEXCEPT ( EPD,EPD[Country],globalproduct[ETIM_class] )

),

EPD[AVG total per ETIM and country],

,

ASC,

DENSE

))

This leaves the corresponding cells in rankx blank, but when calculating  ranking, it does not ignore those blanks, so it did not fix the issue.

[bsz412]

Perfect, thank you!

[MFelix · Accepted solution]

Hi @bsz412 ,

You need to filter out the blanks on the table you are making the calculation for the ranking try the following:

Rank =
RANKX (
    FILTER (
        ADDCOLUMNS (
            CALCULATETABLE (
                EPD,
                ALLEXCEPT ( EPD, EPD[Country], globalproduct[ETIM_class] )
            ),
            "AVGValue", [AVG total per ETIM and country]
        ),
        [AVGValue] <> BLANK ()
    ),
    [AVG total per ETIM and country],
    ,
    ASC,
    DENSE
)