DAX SUM per Category based on another table

greeeniiish — Mon, 06 Feb 2023 12:25:06 GMT

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

Re: DAX SUM per Category based on another table

amitchandak — Tue, 07 Feb 2023 01:30:28 GMT

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

topic DAX SUM per Category based on another table in DAX Commands and Tips

DAX SUM per Category based on another table

Re: DAX SUM per Category based on another table