topic Re: Grouping & combining values from several tables in DAX Commands and Tips

Grouping & combining values from several tables

royales — Thu, 09 Mar 2023 21:01:33 GMT

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

Re: Grouping & combining values from several tables

FreemanZ — Thu, 09 Mar 2023 22:25:51 GMT

hi royales

how are the tables related?

Re: Grouping & combining values from several tables

royales — Fri, 10 Mar 2023 01:51:30 GMT

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

Re: Grouping & combining values from several tables

Anonymous — Tue, 14 Mar 2023 02:50:14 GMT

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