Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |