Sum Distinct Values from different tables in DAX

Question

Hello,&nbsp;I am trying to sum distinct values from 2 tables.&nbsp;Table&nbsp;1 containts the Part numbers shipped from location 1.Table 2&nbsp;containts the Part numbers shipped from location 2.&nbsp;I would like to have a DAX table created that will sum the total quantity shipped of each part number.&nbsp;&nbsp;Any Help is appreciated,Thanks&nbsp;&nbsp;

liviolanzo · Answer

HI&nbsp;soldanr&nbsp;the simplest way to do this is to combine table1 and tabl2, adding an extra column to identify the location.&nbsp;afterwards, you can summarize this&nbsp;easily with a matrix&nbsp;if you wanted to do it with a DAX query to create a new table, you can do it like this:&nbsp;&nbsp;&nbsp;&nbsp;=GROUPBY&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;UNION&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS&nbsp;(&nbsp;Ship1,&nbsp;"Location",&nbsp;"Location&nbsp;1"&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECTCOLUMNS&nbsp;(&nbsp;Ship2,&nbsp;"PN",&nbsp;[PN],&nbsp;"QTYSHP1",&nbsp;[QTYSHP2]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Location",&nbsp;"Location&nbsp;2"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;Ship1[PN],&nbsp;&nbsp;&nbsp;&nbsp;"TOT",&nbsp;SUMX&nbsp;(&nbsp;CURRENTGROUP&nbsp;(),&nbsp;[QTYSHP1]&nbsp;))

soldanr · Answer

Unfortunately I am using direct query and I don't have the option to create a new table.&nbsp;I like the union solution, it is very elegant but I can't make it work :(&nbsp;

Forum Discussion

Sum Distinct Values from different tables in DAX

2 Replies

Related content

Date Different return wrong value

count distinct

Index DISTINCT values

How to cound real distinct values?

Getting distinct values for multiple columns