Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
oakfootballclub
Helper IV
Helper IV

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

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:

oakfootballclub_0-1721962568189.png

 

 

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @oakfootballclub ,

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

vyilongmsft_0-1721978481191.png

Then I create a matrix.

vyilongmsft_1-1721978848078.png

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

vyilongmsft_2-1721978967055.png

Finally you will get what you want.

vyilongmsft_3-1721979004037.png

 

 

 

 

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.

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Hi @oakfootballclub ,

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

vyilongmsft_0-1721978481191.png

Then I create a matrix.

vyilongmsft_1-1721978848078.png

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

vyilongmsft_2-1721978967055.png

Finally you will get what you want.

vyilongmsft_3-1721979004037.png

 

 

 

 

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.