RANK.EQ If statement ignoring 0

[QLD_Jay]

Hi All,

I'm attempting to use the RANK.EQ function to produce TopN and BottomN tables that can be filtered by rank.

Currently I have a TopN column that ranks Device CPU usage totals from highest to lowest with:

RankEq-TopN = RANK.EQ('All Device CPU Rank Table'[Total],'All Device CPU Rank Table'[Total],DESC)

And I'd like to produce the inverse for BottomN.

The issue I encounter is the 25 deactivated devices at the bottom with a CPU usage total of 0 that are all Rank 1 followed by the lowest active device that starts at Rank 26.

I've attempted to ignore the devices with a total of 0 with a pretty amateurish If statement:

RankEq-BottomN = IF('All Device CPU Rank Table'[Total]<>0,RANK.EQ('All Device CPU Rank Table'[Total],'All Device CPU Rank Table'[Total],ASC))

But my Rank value still starts at 26.

Does anyone have any suggestions as to how to ignore a specific value and still start the rank value at 1?

 Edit: Any other suggestions to better acheive the same goal would also be appreciated

[Zubair_Muhammad · Accepted solution]

@QLD_Jay

Try with RANKX

=
IF (
    'All Device CPU Rank Table'[Total] <> 0,
    RANKX (
        FILTER (
            ALL ( 'All Device CPU Rank Table'[Total] ),
            'All Device CPU Rank Table'[Total] <> 0
        ),
        'All Device CPU Rank Table'[Total],
        ,
        ASC
    )
)

[QLD_Jay]

Thanks Zubair,

Worked exactly as required.

I attempted RANKX prior to RANK.EQ and for the life of me couldn't get anything but 1's out of it.

Cheers!