Ranking & Dynamic Title Help

[AaronGlenn10]

I have a matrix with a list of restauraunt locations based on the City in the Selection Slicer.

Trying to accomplish two things:

1.) Only want to see Rankings when a restaurant is selected [in the screenshot below, if the user deselected the restaurnat location then the "Location Rank in City" would go away/disapper; and,

2.) Trying to figure out how to rank the restaurant against all locations in the State.

This measure provides the ranking within the Selected City ->

Location Rank in City = CALCULATE ( If( NOT (ISBLANK ([TABC Sales (CY)])), Rankx (filter (ALL(TABC[Location Name/City/Number]), NOT(isblank([TABC Sales (CY)]))), [TABC Sales (CY)])))

Thank you!

[v-kkf-msft · Accepted solution]

Hi @AaronGlenn10 ,

Please try the following formula:

Location Rank in State = 
CALCULATE (
    [Location Rank in City],
    REMOVEFILTERS ( TABC[City] ),
    KEEPFILTERS ( TABC[State] = MAX ( TABC[State] ) )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AaronGlenn10]

Thank you.

The rank for LOCATION within a CITY [e.g., #5 in the City of Dallas] works great, however, still can't resolve how to rank the LOCATION within the ENTIRE STATE of TEXAS.  Any guidance would be much appreciated.

[amitchandak]

@AaronGlenn10 , Try measures like

Rank = Rankx (ALL(TABC[Location Name/City/Number]), [TABC Sales (CY)])

Location Rank in City = if(isfiltered(TABC[Location Rank in City]), [Rank], blank())