Search code examples
daxssasssas-tabulartabular-editor

DAX - Sum column values from another table grouped by

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.sstatic.net/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.sstatic.net/iJ0Mg.png)

Solution

  • 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 )