DAX - Sum column values from another table grouped by

Question

I'm new to Dax.

I want to create a new calculated column in SSAS (Tabular Editor) that will show the number of products per order.

The ORDER_DET table has a column with the quantity of products per order. For instance, order_nr=100264 will have 4 units.

(https://i.stack.imgur.com/cHvVU.png)

I want basically to have these values summed up so that on the ORDER_CAB table, it shows the total nr of products per order_nr.

I've created the new calculated column, but now I need to obtain the prod_qty sum from the ORDER_DET table.

(https://i.stack.imgur.com/iJ0Mg.png)

Answer 1

You can use below DAX formula in order to create a new calculated column:

Total Number of Products per order = 
VAR name1 = TABLE[Order_NR]
RETURN
    CALCULATE (
        SUM ( TABLE[PROD_QTY] ),
        ALL ( TABLE ),
        FILTER ( TABLE, TABLE[Order_NR] = name1 )

Answer 2

Also if you want to create the column in another table

Column = GROUPBY(ORDER_DET,ORDER_DET[Order_NR],"Total Number of Products per order",
         SUMX(CURRENTGROUP(),ORDER_DET[PROD_QTY]))