RANKX Issue - Giving Similar Sum Amounts same rank

[chilian]

I have been struggling with this for couple days. I am trying to use Power BI to create a simple report to show the rankings.

I have a table called OpenOpportunity that has the following:

Cust ID    Opportunity ID   Opportunity Amt

1               111                    20,000,000

1               112                    3,359,000

2               114                    16,000,000

2               105                    2,000,000

3               118                    16,850,000

4               230                    15,128,000

5               145                    15,000,000

I am trying to sum opportunity values and rank it by Cust ID.

Cust ID       Sum of OppsAmt    Rank

1                 23,359,000                 1

2                 18,000,000                 2

3                 16,850,000                 3

4                 15,128,000                 4

5                 15,000,000                 5

So I created a measure named SumOpps = SUM(OpenOpportunity[Opportunity Amt])

Then, I added another measure named RankbyWinID = RANKX(ALL(OpenOpportunity),[SumOpps],,DESC)

However, the result I got was the following :

Cust ID       Sum of OppsAmt    Rank

1                 23,359,000                 1

2                 18,000,000                 1

3                 16,850,000                 2

4                 15,128,000                 2

5                 15,000,000                 2

Where am I doing wrong?

[OwenAuger · Accepted solution]

HI @chilian

The first argument of RANKX is the table that is used as a reference for determining the rank value. The expression in the second argument is evaluated in the context of each row of that table, creating a list of values , then its value in the current context is compared with that list to determine its rank.

In your case, you want rank Cust IDs, so you should use ALL ( OpenOpportunity[Cust ID] ) as the first argument of RANKX, rather than ALL ( OpenOpportunity ).

This measure should do the trick:

RankbyWinID =
RANKX ( ALL ( OpenOpportunity[Cust ID] ), [SumOpps],, DESC )

Regards,

Owen