Sort Matrix table based on row that is not on top

[Krcmajster]

I have a matrix visual that contains ID,Region,Country,City as rows (to get the Freeze columns effect of Excel). This is the order client wants. However he wants matrix to be sorted by city, which currently does not work if I Sort by it. If I move the City to top of the rows then it is fine. 
Is there a way to sort the visual based on other values that are in the row?  I need to use matrix visual because I can't mimic freeze columns from excel with simple table visual 

[PC2790]

Hello @Krcmajster,

So if I understand correctly, you have City as your subcategory in sequence as ID --> Region -->Country -->City

So when you are trying to do a sort by -->City in the matrix settings, is it not working?

Something like this: 

[Krcmajster]

I don't have the option as you but I am assuming I need to create a hierarchy and add it to Rows to get the Category and subcategory?

[PC2790]

No heirarchy is needed as such.

If you can share your pbix file removing the sensitive data, I can take a look

[Krcmajster]

@PC2790 Unfortunately I can't do that. But based on what you mentioned it is strange that it is not sorted. On the screenshots below you can see that I am doing the same thing, but the order is the same no matter if I select asc or desc

[v-rzhou-msft · Accepted solution]

Hi @Krcmajster 

Matrix visual currently could only be sort by the first row.

So sort by City column makes no sense when your City column is not the the first column in matrix row.

You can try my way, sort your ID column by City column and then sort your matrix by ID.

Your ID column is the first row, it will work.

My Sample:

Select ID in Fields and sort ID by City in Column Tools.

Build a Matrix.

Sort Matrix by ID and select des/asc.

The Matrix will show the result sorting by City.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.