cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Using Rank in a table

Hi,

I'm trying to get to grips with the RANK function but I seem to be having a bit of trouble getting it to work.  I have a table that stores opportunities for both contacts and companies and I would like to get the top ten ranked opportunites for companies only.  To complicate matters slightly, the value column may hold NULL, 0.00 or a positive value (such as 124.16)

So the table would look a little like this:

accountid   contactid   value

1                 Null           12

2                 Null           0.00

Null            1                3

3                 Null           Null

4                 Null           45

Null            2                23

5                 Null           2.3

The output that I would like to achieve is:

accountid   value   rank

4                 45       1

1                 12       2

5                 2.3      3

etc.

Any help would be greatly apreciated!

1 ACCEPTED SOLUTION
Community Champion

@eyeball

hi, please try this: (Is a @Sean version with little changes)

```Ranking =
IF (
FIRSTNONBLANK ( Table1[AccountId], Table1[AccountId] ) <> BLANK (),
RANKX (
FILTER ( ALL ( Table1 ), Table1[AccountId] <> BLANK () ),
CALCULATE ( SUM ( Table1[Value] ) )
)
)```

Lima - Peru
4 REPLIES 4
Community Champion

@eyeball

```Rank Measure =
IF (
HASONEVALUE ( 'Table'[accountid] ),
IF (
ISBLANK ( MIN ( 'Table'[accountid] ) ),
BLANK (),
RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[accountid] <> BLANK () ),
CALCULATE ( SUM ( 'Table'[value] ) )
)
)
)```

Here's the result...

Good Luck!

EDIT: @Vvelarde's IF (... below takes care of the text format @eyeball

(the Rank itself remains the same just the condition is changed)

Frequent Visitor

Thanks for this!! I've been trying to get Rank to work for a while now and this soution was perfect.

Frequent Visitor

Hi Sean,

Many Thanks for this, it looks good!  The only problem I've got now is that the Id is actually a guid (sorry, should have mentioned that) so the min function is seeing it as a string...

Community Champion

@eyeball

hi, please try this: (Is a @Sean version with little changes)

```Ranking =
IF (
FIRSTNONBLANK ( Table1[AccountId], Table1[AccountId] ) <> BLANK (),
RANKX (
FILTER ( ALL ( Table1 ), Table1[AccountId] <> BLANK () ),
CALCULATE ( SUM ( Table1[Value] ) )
)
)```

Lima - Peru

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors