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

Highlighted
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

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 92 members 1,651 guests
Recent signins: