Grouping & combining values from several tables

[royales]

I have 3 tables (for 3 different causes of death). Each table has Country list,  Region, Number of deaths.

How do I create a table /measure that will summarize into something like this

Region	Death A	Death B	Death C
Asia	100	200	300
North America	400	400	500

Thank you. Very new to Power BI

[Anonymous · Accepted solution]

Hi  @royales ,

I created some data:

Table1:

Here are the steps you can follow：

1. Create calculated table.

Table =
var _table1=
SUMMARIZE(
    'Table1','Table1'[Region Name],"DeathA",SUMX(ALL('Table1'),[Death A]),"DeathB",SUMX(ALL('Table1'),[Death B]),"DeathC",SUMX(ALL('Table1'),[Death C]))
var _table2=
SUMMARIZE(
    'Table2','Table2'[Region Name],"DeathA",SUMX(ALL('Table2'),[Death A]),"DeathB",SUMX(ALL('Table2'),[Death B]),"DeathC",SUMX(ALL('Table2'),[Death C]))
var _table3=
SUMMARIZE(
    'Table3','Table3'[Region Name],"DeathA",SUMX(ALL('Table3'),[Death A]),"DeathB",SUMX(ALL('Table3'),[Death B]),"DeathC",SUMX(ALL('Table3'),[Death C]))
return
UNION(
_table1,_table2,_table3)

2. Result:

If I have misunderstood your meaning, please provide your desired output and your pbix file without privacy information.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[royales]

when i put them in power BI, no relationship was created between these tables. I downloaded this data for a project and I got like 5 separate tables. Basically, they all look like this, except for the last column where the XX and Death number changes

Region Name	Country Name	Year	Age Group	Death by XX
South America	Brazil	2019	[20-29]	10
South America	Brazil	2019	[30-39]	50

 And the list goes on til country Z, year 2021, and age group 80, so it's a long list

[FreemanZ]

hi @royales 

how are the tables related?