Rank with two categories

[twintrbl]

I'm trying to rank our Vendors by their average survey scores, but I need it ranked not just by the Vendor, but by the Vendor and the State.  Over-simplified example:

I have tried all kinds of ways to make this work, but I'm ending up with results that make no sense. If I use:

RANKX(
ALL('Dispatch'[Vendor]),
CALCULATE ( AVERAGE ( Survey[Rating]))
)

Even though I have the State in the Table, I can't begin to decipher what it's doing. I have it sorted descending on the Average Rating, but the rankings make no sense.

I'm guessing it's related to not having State reflected in the Rank formula. So I tried creating a new column that combined both Vendor and State into a single column ([Vendor&State]), so I can then refer to it in the formula. 

RANKX(
ALL('Dispatch'[Vendor&State]),
CALCULATE ( AVERAGE ( Survey[Rating]))
)

And it seemed to work.

(Yes, there are 692 that have 5.000 and therefore rank 1, so the next highest is correctly showing as 693.) But I don't want a single ugly column showing the Vendor and State mashed together, and someone might want to sort or filter it by state OR Vendor, so I was trying to add the Vendor and State columns back into the table. I added State and it went back to the crazy rankings in the first screenshot - 1, 65, 72, ....

So now I'm lost and thinking I need to approach this in a totally different way, but I don't know how to use two columns as the ranking category, with one measure (actually the Average of a column value) as the value. Where do I even start?

And this is just the beginning, since I need to add two other measures to the table once I get rank corrected (the # of surveys returned and the response rate), and I'm afraid those will make the rank measure go psychotic again.

Ideas? I'm feeling really stupid because this seems such a basic application of a ranking measure, but nothing is working.

FWIW, I've tried adding an 
IF ( HASONEVALUE ( 'Dispatch'[Vendor&State]),

to the formula, or changing the

ALL() to ALLSELECTED() 

But neither one makes any difference.

Thanks in advance for any suggestions!

[jdbuchanan71 · Accepted solution]

Hello @twintrbl 

You can feed more than one column into your ALL statement which will give you the combined list to rank over.  Give this a try.

Ranking =
RANKX (
    ALL ( 'Dispatch'[Vendor], 'Dispatch'[State] ),
    CALCULATE ( AVERAGE ( Survey[Rating] ) )
)

[twintrbl]

@jdbuchanan71  Thank you! That worked! I knew it had to be something simple. I just never saw any examples with two columns in the ALL statement. Thank you for taking the time to reply!