RANKX without zeros and blanks

[Anonymous]

Hi guys,

quick question:

I want to calculate a rank with RANKX but filter out zeros and blanks.

Any idea ? Filter does not work. 😞

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Please share a sample if the demo below doesn't help.

1. As a calculated column.

Ranks =
IF (
    ISBLANK ( [Value] )
        || [Value] = 0,
    BLANK (),
    RANKX (
        ALL ( 'Table1'[Name], Table1[Value] ),
        CALCULATE ( SUM ( Table1[Value] ) )
    )
)

2. As a measure.

rankMeasure =
VAR temp =
    SUM ( Table1[Value] )
RETURN
    IF (
        temp = 0,
        BLANK (),
        RANKX ( ALL ( Table1[Name] ), CALCULATE ( SUM ( Table1[Value] ) ) )
    )

Best Regards,
Dale

[Anonymous]

@v-jiascu-msft:

Thanks a lot. Helped a lot !:)

[Ashish_Mathur]

Hi,

Share some data and show the expected result.