cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Virtual Launch Event

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 206 members 2,064 guests
Please welcome our newest community members: