Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |