Order matrix columns by a column that exists on another table

[Smackcat]

Hi experts.

I have two tables:

Table1:

Status	Amount
Nice	100
Bad	50

Table2:

Name	Status
Maria	Nice
Mario	Bad

I'm using Table2 to get a matrix, and I want to order its rows with the Amount column in an ascendent order, not the Status column. So my result would be:

Name	Status
Mario	Bad
Maria	Nice

But I do not want to show the Amount column on the matrix. Any advice would be appreciated. Thanks

Pd: I want to add that I can't use the option "order by another column" on table 1 because they have multiple values according to the Amount column. They're unique values per country though

Table1:

Status	Amount	Country
Nice	100	USA
Bad	50	UK
Nice	60	UK
Bad	120	USA

[v-yadongf-msft · Accepted solution]

Hi @Smackcat ，

I'm not sure the relationship between table 1 and table 2, but preferably there is a one to one relationship between them. So I add an index column into both tables.

This is my test table 1:

Table 2:

Create a new column in table 2:

Amount = LOOKUPVALUE('Table 1'[Amount],'Table 1'[Index],'Table 2'[Index])

Sort ascending for amount column:

Create a matrix and it will sort by amount column:

Best regards,

Yadong Fang

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

[HoangHugo]

Hi, I think if can not use " "order by another column", you must put Amount column into your matrix to make sort. You can narrow the Amount column, change its color format to be invisble.