Thanks for the reply. I probably didn't explain myself properly, my fault!
So I have calculated all my rankings in SQL and connected to Tableau. I've created a dashboard, image below, can't upload anything due to the data it's connected to.
As you can see, the Regional Rank is showing 15 out of: 23. The Trust I have selected from the filter falls within Cheshire and Merseyside and therefore I want it to read 15 out of: 20.
I am using max(regional rank) which is why it's showing 23. I understand that. I just can't seem to find a way to say, if Trust A falls within Cheshire then do max rank by the region it falls within, rather than the whole lot.
Does that make sense?