cancel
Showing results for
Did you mean:
New Member

RANKX Issue - Giving Similar Sum Amounts same rank

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

Re: RANKX Issue - Giving Similar Sum Amounts same rank

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

Proud to be a Datanaut!

Super Contributor

Re: RANKX Issue - Giving Similar Sum Amounts same rank

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

Proud to be a Datanaut!

Announcements

Back to School Contest

Engage and empower students with Power BI!

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 239 members 2,917 guests
Recent signins: