RANKX missing a number in ranking due to zeros or lines that shouldn't be considered in formula

[growandgrow]

Hi all,

I am having an issue in Power BI destkop using the RANKX formula - my formula is below:

GURA Rank District = RANKX( ALL('VW_STORE'[District]), [RT GURA %] , , DESC, Dense )

I have a variance expression that takes positive and negative values, and blank expressions will be ranked the same as zero. A tell-tale sign that this has happened is that the "missing" rank occurs between a positive and negative value.

As you can see in the picture below, it appears as if the ranking of '4' has been calculated by values I do not want to be included in my ranking.

I have tried to modify my formula in a variety of different ways to eliminate the missing ranking of 4 but have been unsuccessful. If this is the formula I am using, do you have any ideas on how I could modify it to make it rank correctly using only the numbers that should be considered in the expression (and not zeros?)

Thanks so much.

kc

[v-yanjiang-msft · Accepted solution]

Hi @growandgrow ,

According to your description, I create a sample. Here's my solution.

Modify the formula like this:

Rank =
RANKX (
    FILTER ( ALL ( 'VW_STORE' ), 'VW_STORE'[RT GURA %] <> BLANK () ),
    [RT GURA %],
    ,
    DESC,
    DENSE
)

For the row with blank value, it can still get a rank value, but the rank of other rows will keep continuous.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hemsun]

I am also in the same situation where I need my rankx not to rank blank columns. 

I used the below measure. How to resolve the empty space showing rank 3 

rank = rankx(

    filter(all('rank table'), [Petrol Vehicles Growth measure] <> BLANK()),

[Petrol Vehicles Growth measure],, DESC)

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[growandgrow]

Thank you!!!