how to order the matrix row header based on value of a column with switch statement

[oakfootballclub]

Hi experts, I created a two hierarchical structure column header based on dimension table like this:

id		type		rank
2		Entertainment		10
10		Telecommunication		90
1		Airfare		90
5		Hotel Accommodation		50
6		Meal		20
12		Miscellaneous		90
7		Meal Allowance		21
15		Cash Advance		90
4		Ground Transportation		40
11		External Course		90
3		Gift		30
8		Other Traveling Expense		90
9		Per Diem Allowance		90
14		Medical check		90
16		Total Amount		2
18		Base Currency Amount		1
17		Base Currency		110

to get the correspond value I created a measure:

cal_amount_ =
SWITCH(TRUE(),
SELECTEDVALUE('dim'[id]) =1,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="1") +0,
SELECTEDVALUE('dim'[id]) =2,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="2") +0,
SELECTEDVALUE('dim'[id]) =3,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="3") +0,
SELECTEDVALUE('dim'[id]) =4,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="4") +0,
SELECTEDVALUE('dim'[id]) =5,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="5") +0,
SELECTEDVALUE('dim'[id]) =6,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="6") +0,
SELECTEDVALUE('dim'[id]) =7,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="7") +0,
SELECTEDVALUE('dim'[id]) =8,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="8") +0,
SELECTEDVALUE('dim'[id]) =9,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="9") +0,
SELECTEDVALUE('dim'[id]) =10,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="10") +0,
SELECTEDVALUE('dim'[id]) =11,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="11") +0,
SELECTEDVALUE('dim'[id]) =12,

....

and then the I put the type column into matrix column:

the problem is that I want to sort the id by total amount column value, 

do you have solution to help me resolve it? Thank you.

[Anonymous · Accepted solution]

Hi @oakfootballclub ,

I created a similar form to accomplish what you want to accomplish.

Then I create a matrix.

If you want to change the matrix row header based on value of a column, I think you can take the steps below.

Finally you will get what you want.

Best Regards

Yilong Zhou

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