Have you tried to achieve with nested sorting? Here is some info how to get to the result you are looking for: Finding the Top N Within a Category | Tableau Software

Also, in case you don't want to show combined file, you can simple bring each dimension and hide combined field.

Here is video instruction if above link is too much :-)

Nested Sorting in Tableau - YouTube

Hello Mahesh,

What you are looking for is a nested ranking.

Taking the example that you quoted:

Put States as rows and sales as columns - and sort descending - this will give you the rank based on states.

As you mentioned inf you insert cities with it, the rank gets messed up.

In this case all you have to do it take the dimensions, Sates and Cities, and make a combined field.

Now, remove cities that you had before, get the combined field of (States and cities). You will notice the change. and the ranking will be maintained.

Thanks,

Ranjit

ok, Try this.

Put the dimension State first in the rows and then get your measure in the columns. Sort by Measure, either ascending or descending.

then put the combined dimension in the rows beside state. you will notice that the city values are sorted within the state, that is already sorted. Try adding the city next to the combined dimension and then hide the combined dimension. you should achieve what you are looking for.

If it doesnt work as you desire, then attach the workbook, let me have look into that.

Thanks,

Ranjit.

Will this meet your expectations as seen below?

All above dimensions have computed sorting based on Sales, descending.

ok Mahesh.. try this:

you have created the State+city combined field.

Now create this cal func: "rank"  with the formula -  RANK_DENSE(sum({fixed [State]: sum([Invoice])}), 'desc')

now, arrange the dimensions and the cal fields as per this:

under marks: go to Rank cal and edit Table calc: as shown below:

Now when you expand or collapse the state.. the order will be maintained as per your choice.

Try and let me know.

Thanks,

Ranjit

It worked perfect as seen below:

Region:

State:

City: (Combined State and City)

Zip code:

Instructions:

Expand all hierarchy members and sort them by SUM(Sales) as seen below for Postal Code:

Next: Combine State and City together and replace it with original City dimension. That's all.

Mahesh, did you try sorting it.

For me it is working like a cake.