extracting information from a many to many relationship

[CL7777]

hi all, 

I consider myself an advanced beginner dax user and Im having a hard time with this one. suppose i have 2 tables: order table and sales table as below. the red numbers represent the values I want to calculate in a calculated column.

order table                                                                       sales table

order #   kit flag  amount                                             order #   summed amt

1                 c           $11                                                    1              $24

2                 c           $12                                                    1              $24

1                 c           $13                                                    2              $12

2                 p           $14                                                    3              $0

3                 p           $15                                                    3              $0

4                 p           $16                                                    3              $0

4                 c           $17                                                     4              $35

4                 c           $18                                                     4              $35

1                 p           $19                                                    1              $24

The two tables are connected through a many to many relationship on the order number field. what i want to do is create a calculated column in the sales table that sums up the amount in the order table for any matching order number that has a kit flag of "c". 

I apppreciate the help

[pranit828 · Accepted solution]

Hi @CL7777 

Kit C sum  = Calculate(SUM('order'[amount]),sales[Order#]=order[order#] && order[kit flag]="c")

or

Kit C sum  = Calculate(SUM('order'[amount]),FILTER(ALLEXCEPT(sales[Order#]),order[kit flag]="c"))

[amitchandak]

@CL7777 , Try a formula like, new column in sales

sumx(filter(Order,Order[Order#] = Sales[Order#] && Sales[kit flag] ="c"),Order[amount])

[Ashish_Mathur]

Hi,

Try this calculated column formula in the Sales table

=calculate(sum('Order'[amount]),filter('order','order'[order #]=earlier('order'[order #])&&'order'[kit flag]="c"))

Hope this helps.

[V-lianl-msft · Accepted solution]

Hi @CL7777 ,

Try this:

summed amt =
VAR amt =
    CALCULATE (
        SUM ( 'Order Table'[amount] ),
        FILTER (
            ALLEXCEPT ( 'Order Table', 'Order Table'[order#] ),
            'Order Table'[kit flag] = "c"
        )
    )
RETURN
    IF ( ISBLANK ( amt ), 0, amt )

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pranit828 · Accepted solution]

Hi @CL7777 

Kit C sum  = Calculate(SUM('order'[amount]),sales[Order#]=order[order#] && order[kit flag]="c")

or

Kit C sum  = Calculate(SUM('order'[amount]),FILTER(ALLEXCEPT(sales[Order#]),order[kit flag]="c"))