Sum Distinct Values from different tables in DAX

[soldanr]

Hello,

I am trying to sum distinct values from 2 tables.

Table 1 containts the Part numbers shipped from location 1.

Table 2 containts the Part numbers shipped from location 2.

I would like to have a DAX table created that will sum the total quantity shipped of each part number.

Any Help is appreciated,

Thanks

[LivioLanzo]

HI @soldanr

the simplest way to do this is to combine table1 and tabl2, adding an extra column to identify the location. 

afterwards, you can summarize this easily with a matrix

if you wanted to do it with a DAX query to create a new table, you can do it like this:

=
GROUPBY (
    UNION (
        ADDCOLUMNS ( Ship1, "Location", "Location 1" ),
        ADDCOLUMNS (
            SELECTCOLUMNS ( Ship2, "PN", [PN], "QTYSHP1", [QTYSHP2] ),
            "Location", "Location 2"
        )
    ),
    Ship1[PN],
    "TOT", SUMX ( CURRENTGROUP (), [QTYSHP1] )
)

[soldanr]

Unfortunately I am using direct query and I don't have the option to create a new table.

I like the union solution, it is very elegant but I can't make it work 😞