Forum Discussion

eyeball's avatar
eyeball
Frequent Visitor
9 years ago
Solved

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!

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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] ) )
    )
)

     

4 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    eyeball

     

    How about this...

    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! :smileyhappy:

     

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

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

     

    • eyeball's avatar
      eyeball
      Frequent Visitor
      9 years ago

      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...

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        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] ) )
    )
)

         

    • WizardWalksBy's avatar
      WizardWalksBy
      Frequent Visitor
      9 years ago

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