DAX SUM per Category based on another table

[greeeniiish]

Hi everyone,

I have two tables Summary and Sales. Summary has three columns "Entity", "Client", "Location". Sales has three columns "Entity", "Client", and "Amount". Both tables are connected using two Dim tables through the "Entity" column and another one based on the "Client" column.

The Sales table has more clients than Summary e.g. Client A can exists in Sales but not Summary. I would like to get the total Sum of each "Entity" from the Sales table and use it in the Summary table (regardless of the filter context).

Just to clarify, the name of the Entities on both sides are the same but Sales can have more clients that Summary, so a simple relation between the two would results in loss of some of the client "Amount"s.

Would you please guide me on a DAX measure or calculated column (I'd rather not do anything on the Power Query side).

Thanks

[amitchandak]

@greeeniiish , A new column in summary

sumx( filter(sales, sales[entity] = summary[entity] ), sales[Amount])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8