Subheaders from two tables in PowerBI Matrix Table

[BIUser1998]

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!

[Anonymous · Accepted solution]

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.

[BIUser1998]

@amitchandak Thank you for the valuable input!

[Anonymous · Accepted solution]

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.

[BIUser1998 · Accepted solution]

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]

@BIUser1998 , Option 1 append two tables in Power query

Append Tables (Power Query)
https://www.youtube.com/watch?v=KyXIDInZMxk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=15

Or 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-soluti...

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw