cancel
Showing results for
Did you mean:
AlexBro Frequent Visitor

## RANKX without zeros and blanks

Hi guys,

quick question:

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

Any idea ? Filter does not work. 1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: RANKX without zeros and blanks

Hi @AlexBro,

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3 Super User

## Re: RANKX without zeros and blanks

Hi,

Share some data and show the expected result. Community Support Team

## Re: RANKX without zeros and blanks

Hi @AlexBro,

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
AlexBro Frequent Visitor