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

[chudson]

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 = 

RANKX(ALLSELECTED(DimSalesAgent[DISTRICT]),[% to Quota],,DESC)

Also, to note my QTD, QTD Quota, % to Quota are all calculated measures as well.

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!

[v-lili6-msft · Accepted solution]

hi, @chudson 

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/4400...

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

[chudson]

@v-lili6-msft 

Thanks! that formula helps so much!