Forum Discussion
Subheaders from two tables in PowerBI Matrix Table
I have two data tables
| Category | Class | Value | Fiscal Year |
| America | A | 35 | 2022 |
| Canada | A | 49 | 2022 |
| France | A | 12 | 2022 |
| China | A | 324 | 2022 |
| America | B | 75 | 2022 |
| Canada | B | 12 | 2022 |
| France | B | 21 | 2022 |
| China | B | 63 | 2022 |
and
| Category | Class | Value | Fiscal Year |
| America | A | 45 | 2023 |
| Canada | A | 59 | 2023 |
| France | A | 22 | 2023 |
| China | A | 334 | 2023 |
| America | B | 85 | 2023 |
| Canada | B | 22 | 2023 |
| France | B | 31 | 2023 |
| China | B | 73 | 2023 |
And I want a Matrix table to look like
I tried a couple of things but they didn't work out. Any ideas as to how I can achieve this? Also, is it possible to have one table by merging the two tables based on the category table?
Thank you for the help!
Hi BIUser1998 ,
I’d like to acknowledge the valuable input provided by the amitchandak . His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. In my investigation, I took the following steps:
I create two tables as you mentioned.
Then I go to the Power Query and use the Appended Queries.
Then I create two measures named A and B.
A = MAXX(FILTER('T1','T1'[Class]="A"),'T1'[Value])B = MAXX(FILTER('T1','T1'[Class]="B"),'T1'[Value])Finally I use the matrix visual and 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 Anonymous, this worked like a charm. I have an additional doubt though. Since there are two tables, there are some rows common and some distinct rows, is there any way I can show in which all tables which country is available? Something like shown in the screenshot attached?
4 Replies
BIUser1998 , Option 1 append two tables in Power query
Append Tables (Power Query)
https://www.youtube.com/watch?v=KyXIDInZMxk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=15Or create common dimensions like Category , class and fiscal year and join with both tables and use them for analysis
Example dim
Category = distinct(union(distinct(Table1[Category]),distinct(Table2[Category])))
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bdaPower BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw
Hi BIUser1998 ,
I’d like to acknowledge the valuable input provided by the amitchandak . His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. In my investigation, I took the following steps:
I create two tables as you mentioned.
Then I go to the Power Query and use the Appended Queries.
Then I create two measures named A and B.
A = MAXX(FILTER('T1','T1'[Class]="A"),'T1'[Value])B = MAXX(FILTER('T1','T1'[Class]="B"),'T1'[Value])Finally I use the matrix visual and 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 Anonymous, this worked like a charm. I have an additional doubt though. Since there are two tables, there are some rows common and some distinct rows, is there any way I can show in which all tables which country is available? Something like shown in the screenshot attached?
amitchandak Thank you for the valuable input!
