cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexBro Frequent Visitor
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. Smiley Sad

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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] ) ) )
    )

RANKX-without-zeros-and-blanks

 

 

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
Super User

Re: RANKX without zeros and blanks

Hi,

 

Share some data and show the expected result.

Community Support Team
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] ) ) )
    )

RANKX-without-zeros-and-blanks

 

 

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
Frequent Visitor

Re: RANKX without zeros and blanks

@