Combine data column from different table

[aanmi]

Hi

May I know is there any way to combine same data column into one table?

For example I have created three different table as below

and I want to combine all data to be visualize in one table as below: 

Kindly advise how can I create combined data table asshown above since I already try to use UNION DAX command before but the result is not same.

[MattAllington]

For each table, add a new column identifying the table (either Maths, science, physics)

call this column "subject" in all cases

append the 3 tables together

pivot the first 2 columns, with sum on the numeric column

[aanmi]

Hye Matt

Could you explain how can I add another column with same record for more than 3000 rows for each table?

[MattAllington]

Select "add custom column"

enter ="my value" in the formula box

[aanmi]

Hi Matt

I managed to add custom column as you advised but unfortunately after I try to pivot the first 2 column I didnt get the result that I want.,

What do you mean by "pivot the first 2 columns, with sum on the numeric column"

Do I need to pivot country name column and subject column for all three tables?

And how to do - sum on the numeric column as you advised earlier?

[MattAllington · Accepted solution]

First add the new column in each table. You should the  have 3 tables with 3 columns.  All columns have the same column headings

then append the tables into 1 long table. 

When end you have 1 table, then unpivot the country and subject columns. When you do this, you will be asked which column to aggregate, and how. Select sum of the numeric column