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,
I'm hoping someone can help me here with ranking a report visual table. Below is an example of the data I'm showing as a table visual in a report and I have a rankx function that ranks the districts by % to quota but I want a 2nd ranking measure in here to rank the districts by their QTD Quota # that would break a tie if the % to quota were the same (in this case a lot of the districts with 0 % to quota).
So instead of ranking all Districts in my example as 12, the rank would look at 0% of % to Quota and thank rank by the QTD Quota #.
My Rank Column is calculated measure =
RANK | DISTRICT | QTD Sales | QTD Quota | % to Quota |
1 | District 1 | $55,582 | $50,770 | 109% |
2 | District 2 | $347,563 | $187,500 | 185% |
3 | District 3 | $32,206 | $87,844 | 37% |
4 | District 4 | $194,298 | $223,779 | 87% |
5 | District 5 | $514,625 | $490,000 | 105% |
6 | District 6 | $979,158 | $1,056,843 | 93% |
7 | District 7 | $186,375 | $264,327 | 71% |
8 | District 8 | $724,728 | $847,000 | 86% |
9 | District 9 | $57,059 | $140,273 | 41% |
10 | District 10 | $610,496 | $750,001 | 81% |
11 | District 11 | $414,430 | $547,200 | 76% |
12 | District 12 | $0 | $10,613 | 0% |
12 | District 13 | $0 | $140,000 | 0% |
12 | District 14 | $0 | $116,752 | 0% |
12 | District 15 | $5,400 | $0 | 0% |
12 | District 16 | $0 | $119,834 | 0% |
12 | District 17 | $25,370 | $0 | 0% |
12 | District 18 | $0 | $0 | 0% |
12 | District 19 | $0 | $75,000 | 0% |
12 | District 20 | $0 | $186,678 | 0% |
Thanks!
Solved! Go to Solution.
hi, @chudson
You could refer to this same post:
And for your case, just use this formula:
Final Rank = RANKX ( ALLSELECTED(DimSalesAgent[DISTRICT]), RANKX(ALLSELECTED(DimSalesAgent[DISTRICT]),[% to Quota],,DESC) + DIVIDE ( RANKX(ALLSELECTED(DimSalesAgent[DISTRICT]),[QTD Quota],,DESC), ( COUNTROWS ( ALLSELECTED(DimSalesAgent[DISTRICT]) ) + 1 ) ) ,,ASC)
Result:
Best Regards,
Lin
hi, @chudson
You could refer to this same post:
And for your case, just use this formula:
Final Rank = RANKX ( ALLSELECTED(DimSalesAgent[DISTRICT]), RANKX(ALLSELECTED(DimSalesAgent[DISTRICT]),[% to Quota],,DESC) + DIVIDE ( RANKX(ALLSELECTED(DimSalesAgent[DISTRICT]),[QTD Quota],,DESC), ( COUNTROWS ( ALLSELECTED(DimSalesAgent[DISTRICT]) ) + 1 ) ) ,,ASC)
Result:
Best Regards,
Lin
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |