Maintain Rank after Filtering (Tableau)

This table is showing states with sales, using RANK() function to rank highest sales.  The requirement is to have this filter using this formula:

 

@Profit Ratio & Sales Per Customer

IF [Profit Ratio] >= 0.3 and [Sales per Customer] >= 700 

then "Excellent Profit Ratio & Sales/Customer" 

ELSE "OK Profit Ratio & Sales/Customer" END

 

Issue:  When this formula is used as filter and selected for 'Excellent Profit Ratio & Sales/Customer', the ranking is changed for the states, i.e., Michigan is ranked 1, Indiana 2, Delaware 3...  But this is not correct, we would like to keep the ranking the same after filtering, i.e., Michigan 9, Indiana 13, Delaware 21...

 

Solution:  Change formula above to a table calculation filter using LOOKUP function with a 0 offset.  

 

@Profit Ratio & Sales Per Customer (LOOKUP)

LOOKUP (

IF [Profit Ratio] >= 0.3 and [Sales per Customer] >= 700 

then "Excellent Profit Ratio & Sales/Customer" 

ELSE "OK Profit Ratio & Sales/Customer" END

,0)

 

This change will make the formula a table calculation filter, which in the Order of Operations is after table calculation.  So RANK() as a table calculation will take precedence over the table calculation filter.