Forum Discussion
Summarizing values from different tables
Hi, need some help. I have two tables - Internet Sales and Reseller Sales. The common field is the OrderDateKey. Both tables include PTI, SalesAmount, OrderQuantity and TotalProductCost. How to combine on the matrix table those values as TotalSalesAmount, TotalOrderQuantity etc.?
Thank you for any help.
J,
harry6810 , If this a many to many join. then better you do union and summarize
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
example
summarize(union(selectcolumns('Table'1,"Month",'Table'1[Month],"Figure",'Table'1[Figure],"Figure2",0),
selectcolumns('Table'2,"Month",'Table'2[Month],"Figure",0,"Figure2",'Table'2[Figure])),
[Month],"Figure", sum([Figure1]),"Figure2", sum([Figure2]))or append and aggregate in power query
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table
1 Reply
harry6810 , If this a many to many join. then better you do union and summarize
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
example
summarize(union(selectcolumns('Table'1,"Month",'Table'1[Month],"Figure",'Table'1[Figure],"Figure2",0),
selectcolumns('Table'2,"Month",'Table'2[Month],"Figure",0,"Figure2",'Table'2[Figure])),
[Month],"Figure", sum([Figure1]),"Figure2", sum([Figure2]))or append and aggregate in power query
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table