how to order the matrix header with two hierarchical structures

[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, can you elaborate in detail to help me solve it? Thank you.

[Sergii24]

From my experience the answer is: you can't.
For PowerBI there is just one measureI cal_amount_ . It simply returns you different results based on filter context, therefore "cal_amount_ " behaves as unique element.

I worked on a similar project and used similar approach that gave me a lot of issues... My advice is to show measures independetnly so you can filter visuals based on a specific measure.

I hope my answer will help you! Good luck 🙂