Using RANKX or DAX measure to creating a ranking based on 2 calculated measures

Question

Hi,&nbsp;I'm hoping someone can help me here with ranking a report visual table.&nbsp; 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 #.&nbsp;My Rank Column is calculated measure =&nbsp;RANKX(ALLSELECTED(DimSalesAgent[DISTRICT]),[% to Quota],,DESC)&nbsp;Also, to note my QTD, QTD Quota, % to Quota are all calculated measures as well.&nbsp;&nbsp;RANKDISTRICTQTD SalesQTD Quota% to Quota1District 1$55,582$50,770109%2District 2$347,563$187,500185%3District 3$32,206$87,84437%4District 4$194,298$223,77987%5District 5$514,625$490,000105%6District 6$979,158$1,056,84393%7District 7$186,375$264,32771%8District 8$724,728$847,00086%9District 9$57,059$140,27341%10District 10$610,496$750,00181%11District 11$414,430$547,20076%12District 12$0$10,6130%12District 13$0$140,0000%12District 14$0$116,7520%12District 15$5,400$00%12District 16$0$119,8340%12District 17$25,370$00%12District 18$0$00%12District 19$0$75,0000%12District 20$0$186,6780%&nbsp;Thanks!

v-lili6-msft · Accepted Answer

hi,&nbsp;chudson&nbsp;
You could refer to this same post:
https://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/td-p/44008
&nbsp;
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:

&nbsp;
Best Regards,
Lin

Forum Discussion

Using RANKX or DAX measure to creating a ranking based on 2 calculated measures

2 Replies

Related content

Need help creating a measure

Help to create DAX Measure

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Need help to create Calculation in DAX

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%