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.
Hi Guys,
I'm trying to show the TOP 5 only (no ties and not multiple values for a rank i.e. 1,2,2,2,3,4,5).
In this case, I want to display the TOP 5 sales rep for a given month by the total sales count and break ties by the total value of the contracts.
The issues I'm having is that RANX doesn;t handle the ties the need I want (unique IDs)
this is what i get with RANKX:
DATE TOTAL SALES TOTAL ACV RANK RANK(SKIP) RANK NEEDED:
2017-01-01 10 10,000 1 1 1
2017-01-01 8 6,000 2 2 2
2017-01-01 6 9,000 3 3 3
2017-01-01 6 8,000 3 3 4
2017-01-01 6 7,000 3 3 5
2017-01-01 4 5,000 4 6 6
2017-01-01 4 4,000 4 6 7
2017-01-01 2 9,000 3 8 8
2017-02-01 4 5,000 1 1 1
2017-02-01 4 4,000 1 1 2
2017-02-01 2 9,000 2 3 3
2017-03-01 6 5,000 1 1 1
2017-03-01 5 4,000 2 2 2
2017-03-01 5 3,900 2 2 3
.....
thids is the formula i'm currently using:
Rank_NewDealsAccounting = RANKX(FILTER(MONTHLY_ACTIVITY,MONTHLY_ACTIVITY[DATE]=EARLIER(MONTHLY_ACTIVITY[DATE])),MONTHLY_ACTIVITY[TOTAL WINS ACCOUNTING] ,,,Skip)
any help would be really appreciated.
thank you!
ps. i was able to get what i wanted on MYSQL but i wasnt able to use variable in the power bi query editory
Solved! Go to Solution.
Hi @NormnSG,
A pattern can be used in this situation is:
Final value to be ranked =
Rank on Primary Measure (ascending)
+ Rank on Secondary Measure (ascending) / (Total Row Count + 1)
So the formula below should work in your scenario.
Final Rank = RANKX ( FILTER ( MONTHLY_ACTIVITY, MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] ) ), RANKX ( FILTER ( MONTHLY_ACTIVITY, MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] ) ), MONTHLY_ACTIVITY[TOTAL SALES], , ASC ) + DIVIDE ( RANKX ( FILTER ( MONTHLY_ACTIVITY, MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] ) ), MONTHLY_ACTIVITY[TOTAL ACV], , ASC ), ( COUNTROWS ( FILTER ( MONTHLY_ACTIVITY, MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] ) ) ) + 1 ) ) )
Regards
Hi @NormnSG,
A pattern can be used in this situation is:
Final value to be ranked =
Rank on Primary Measure (ascending)
+ Rank on Secondary Measure (ascending) / (Total Row Count + 1)
So the formula below should work in your scenario.
Final Rank = RANKX ( FILTER ( MONTHLY_ACTIVITY, MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] ) ), RANKX ( FILTER ( MONTHLY_ACTIVITY, MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] ) ), MONTHLY_ACTIVITY[TOTAL SALES], , ASC ) + DIVIDE ( RANKX ( FILTER ( MONTHLY_ACTIVITY, MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] ) ), MONTHLY_ACTIVITY[TOTAL ACV], , ASC ), ( COUNTROWS ( FILTER ( MONTHLY_ACTIVITY, MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] ) ) ) + 1 ) ) )
Regards
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 |