Forum Discussion

kfinn's avatar
kfinn
Frequent Visitor
9 years ago
Solved

RANKX- Rank By User

Hi Everyone- I am new to DAX and I have read a handful of blogs on RANKX, but I still can't figure out what I am doing wrong on the below scenario: I want to create a column that ranks user's o...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    kfinn

    How about this?

    Owner Opportunity Rank =
RANKX (
    ALL ( opportunities[Opportunity Name] ),
    CALCULATE (
        SUM ( opportunities[Expected Annual Revenue] ),
        ALLEXCEPT (
            opportunities,
            opportunities[_ownerid_value],
            opportunities[Opportunity Name]
        )
    )
)

     

    EDIT: You want to Rank the Opportunity Names for each Owner!

    I think that's what you meant? :smileyhappy:

Sean's avatar
Sean
Community Champion
9 years ago

kfinn

Your RANK formula works as a MEASURE not a COLUMN

 

If you want a column in the Data View - this should work

Owner Opportunity Rank =
RANKX (
    ALL ( opportunities[_ownerid_value] ),
    CALCULATE (
        SUM ( opportunities[Expected Annual Revenue] ),
        ALLEXCEPT ( opportunities, opportunities[_ownerid_value] )
    )
)

Good Luck! :smileyhappy:

 

kfinn's avatar
kfinn
Frequent Visitor
9 years ago

Thanks Sean for the quick reply.

 

I think this is on the right track, but I believe this is ranking the owner himself.  I am looking for the opportunity rank by the user.  The rank should be determined by expected revenue.  So I am looking for the "Owner Opportunity Rank" column here..

 

_ownerid_valueExpected Annual RevenueOpportunity NameOwner Opportunity Rank
aaaa-1 $                                              100.00Opp12
aaaa-1 $                                              200.00Opp21
aaaa-2 $                                              400.00Opp32
aaaa-2 $                                              500.00Opp41

 

Owner = aaaa-1 has two opportunites.  Opp2 has a greater revenue than Opp1 so the Onwer Opportunity Rank = 1 for Opp2 and so forth...  I apologize if that is not clear in my first post.

 

I when I read the RANKX documentation, I feel like this should be the column:

Owner Opportunity Rank = RANKX (
ALL ( opportunities[_ownerid_value] ),
opportunities[Expected Annual Revenue]
)

 

But I still get all 1's...

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    kfinn

    How about this?

    Owner Opportunity Rank =
RANKX (
    ALL ( opportunities[Opportunity Name] ),
    CALCULATE (
        SUM ( opportunities[Expected Annual Revenue] ),
        ALLEXCEPT (
            opportunities,
            opportunities[_ownerid_value],
            opportunities[Opportunity Name]
        )
    )
)

     

    EDIT: You want to Rank the Opportunity Names for each Owner!

    I think that's what you meant? :smileyhappy:

    • kfinn's avatar
      kfinn
      Frequent Visitor
      9 years ago

      Yes- This is what I am looking for.  I am not following how DAX is grouping by the _ownerid_value but now that I have the acceptable solution, I can play around with it.  Much thanks to you!!