So, unlike Excel, you can't create calculations in one sheet and reference them in another. Also when you are using Aggregate calculations (SUM/MIN/MAX...etc) or Table Calculations (WINDOW_SUM, RANK...etc.) the level of detail in your sheet (known as the VizLoD) will affect the result. This means in order to use the RANK function, and get it to RANK each State by Sales in a Sub-Category, you need to have both Sub-Category and State in the VizLoD. Let me know if that doesn't make sense and I can point you to a few resources (although the on-demand videos on calculations are very good at explaining the different calculation types in Tableau).
Once we have this, building up a string is fairly straight forward as we can use + to concatenate parts together.
So in my example here I have the following formula
[Sales Rank Text]
IF RANK(SUM([Sales]),'desc') > 20 THEN NULL ELSE
ATTR([State]) + ' is ranked ' + str(RANK(SUM([Sales]),'desc')) + ' for ' + ATTR([Sub-Category])
I've had to wrap the State and Sub-Cat in am ATTR as the RANK is a table calculation (and so Aggregates) and I can't mix aggregates and row-level calculations. As we have both State and Sub-Category in the VizLoD ATTR([State]) is just the State.
Hope that helps and makes sense.
Thanks Simon! This worked beautifully! I did have an issue with Aliases not being referenced in the calc results, but I managed to find a workaround by separating the first portion: "IF RANK(SUM([Sales]),'desc') > 20 THEN NULL", out and just filtering the number of ranks I wanted to show.