cancel
Showing results for
Did you mean:
Highlighted
thampton Member

## Rank accounting for 0

I am using RANKX to display a rank based on a value. The value can either be positive or negative, but there will be some with no value (which will show zero). I would like to be able to rank based on highest to lowest, then putting all the blank/0's at the end.

Current:

Value      Rank

5                          1

2                          2

0 (BLANK)            3

-1                         4

-2                         5

Desired Outcome

5                           1

2                           2

-1                          3

-2                          4

0 (BLANK)              5

1 ACCEPTED SOLUTION

Accepted Solutions
v-lid-msft Super Contributor

## Re: Rank accounting for 0

Hi @thampton ,

We can create a measure using following formula to meet your requirement:

```Rank =
VAR t =
FILTER ( ALLSELECTED ( 'Table' ), [Value] + 0 <> 0 )
RETURN
IF (
MAX ( 'Table'[Value] ) + 0 <> 0,
RANKX ( t, CALCULATE ( MAX ( 'Table'[Value] ) ),, DESC ),
CALCULATE ( DISTINCTCOUNTNOBLANK ( 'Table'[Value] ), t ) + 1
)``` We can also create a calculate column using following formula

```RankColumn =
VAR t =
FILTER ( 'Table' , [Value] + 0 <> 0 )
RETURN
IF (
[Value] + 0 <> 0,
RANKX ( t, [Value],, DESC ),
CALCULATE ( DISTINCTCOUNTNOBLANK ( 'Table'[Value] ), t ) + 1
)```  BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
smpa01 Senior Member

## Re: Rank accounting for 0

Hello @thampton  not sure how did you want to solve this problem. Were you hoping to have a solution through a table, measure or calculated column?

I have done it by doing a table and measure

Soucre Data

Column1

 5 2 0 -1 -2 0

Table

```CALTBL =
VAR _1 = FILTER(ALLNOBLANKROW('Table'[Column1]),'Table'[Column1]+0<>0)
VAR _3 = MAXX(_2,[Rank])+1
VAR _4 = FILTER('Table','Table'[Column1]+0+1=1)
VAR _6 = UNION(_2,_5)
RETURN _6``` Through a measure

```Measure: =
VAR _11 = RANKX(FILTER(ALLNOBLANKROW('Table'[Column1]),'Table'[Column1]+0<>0),CALCULATE(SUM('Table'[Column1])),,DESC)
VAR _1 = FILTER(ALLNOBLANKROW('Table'[Column1]),'Table'[Column1]+0<>0)
VAR _3 = MAXX(_2,[Rank])+1
VAR _14 =SUM('Table'[Column1])
VAR _15 = IF(ISBLANK(_14)=True||_14=0,_3,_11)
RETURN _15``` v-lid-msft Super Contributor

## Re: Rank accounting for 0

Hi @thampton ,

We can create a measure using following formula to meet your requirement:

```Rank =
VAR t =
FILTER ( ALLSELECTED ( 'Table' ), [Value] + 0 <> 0 )
RETURN
IF (
MAX ( 'Table'[Value] ) + 0 <> 0,
RANKX ( t, CALCULATE ( MAX ( 'Table'[Value] ) ),, DESC ),
CALCULATE ( DISTINCTCOUNTNOBLANK ( 'Table'[Value] ), t ) + 1
)``` We can also create a calculate column using following formula

```RankColumn =
VAR t =
FILTER ( 'Table' , [Value] + 0 <> 0 )
RETURN
IF (
[Value] + 0 <> 0,
RANKX ( t, [Value],, DESC ),
CALCULATE ( DISTINCTCOUNTNOBLANK ( 'Table'[Value] ), t ) + 1
)```  BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft Super Contributor

## Re: Rank accounting for 0

Hi @thampton ,

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content. #### Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones. #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 182 members 1,761 guests
Recent signins:
• TaylorN • danextian • jkielo • eread • dan_vib • gabereal • shadho • Phteven • Shr • Ariahannah • nborhanipour • tandtkg • sxc 