RANKX- Rank By User

[kfinn]

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 opportunities by the revenue (Owner Opportunity Rank).  Here are the desired results:

_ownerid_value	Expected Annual Revenue	Opportunity Name	Owner Opportunity Rank
aaaa-1	$                                              100.00	Opp1	2
aaaa-1	$                                              200.00	Opp2	1
aaaa-2	$                                              400.00	Opp3	2
aaaa-2	$                                              500.00	Opp4	1

Here is my code:

I created a measurement:

TotalRevenue = SUM(opportunities[Expected Annual Revenue])

I created the desired column

Owner Opportunity Rank = RANKX (ALL(opportunities[_ownerid_value]), [TotalRevenue])

The issue is my "Owner Opportunity Rank" column returns all 1's.

Any help is appreciated!

[Sean]

@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!

[kfinn]

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_value	Expected Annual Revenue	Opportunity Name	Owner Opportunity Rank
aaaa-1	$                                              100.00	Opp1	2
aaaa-1	$                                              200.00	Opp2	1
aaaa-2	$                                              400.00	Opp3	2
aaaa-2	$                                              500.00	Opp4	1

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 · Accepted solution]

@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?

[kfinn]

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

[Phil_Seamark]

You could try this approach.  It might work so let me know how you get on

This Col Might work = CALCULATE( COUNTROWS('Opportunities'),
                FILTER(
                        ALL('Opportunities'),
                        'Opportunities'[_ownerid_value]=EARLIER('Opportunities'[_ownerid_value])
                        && 'Opportunities'[Expected Annual Revenue] < EARLIER('Opportunities'[Expected Annual Revenue])
                         ))+1