Forum Discussion
Grouping & combining values from several tables
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
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
3 Replies
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
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
