cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chilian New Member
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
OwenAuger Super Contributor
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
OwenAuger Super Contributor
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 239 members 2,917 guests
Please welcome our newest community members: