Sum from another table

[Olwin]

Hi there,

I have a table relation like below:

1. Location (Loc Code)

2. Item (Item Code)

3. Shipment Planning (Planning No, Loc Code, Item Code, Quantity)

• Relationship with Location = Many-to-One (Location.Loc Code = ShipmentPlanning.Loc Code)
• Relationship with Item = Many-to-One (Item.Item Code = ShipmentPlanning.Item Code)

4. Order Lines (Order No, Item Code, Loc Code)

• Relationship with Location = Many-to-One (Location.Loc Code = OrderLines.Loc Code)
• Relationship with Item = Many-to-One (Item.Item Code = OrderLines.Item Code)

My goal is to show total "Quantity" from "Shipment Planning" for each "Order Line" with the same Item and Location.

Kindly please help 😊

Thanks.

Sample Data

Location (A & B)

Item (X1 & X2)

Shipment Planning

Shipment Planning No	Item	Location	Quantity
SP#01	X1	A	10
SP#02	X2	A	20
SP#03	X1	B	30
SP#04	X1	A	17

Order Lines

Order No	Item	Location	Total Shipment Planning
SO#01	X1	A	27
SO#02	X2	A	20
SO#03	X1	A	27
SO#04	X1	B	30

[speedramps · Accepted solution]

Download this exampl PBIX solution from Onedrive

Click here 

Please click thumbs up for the suggestion,

and click [accept solution] if it works.

Your question is a bit vague, always provide an example desired output to clarify what is required.

[Olwin]

Hi @speedramps  I have see your given example file, and found that the key to get correct result is the selection of "Item No."

Previously, I show "Item No." in my visual from my "Order Lines" table, but after I change the source table for "Item No." from Item table, the total is correct automatically.

Thanks for your help 😊

[speedramps]

@Olwin thank you.

Yes you understand correctly.

That is why I provide a diagram of the relationships and highlighted in red which fields you needed to select from which tables to use those relationships correctly.

For me the joy cones from not just giving a solution but teaching the method.

Well done on learning it!

[Bibiano_Geraldo]

Hi @Olwin ,

To achieve your goal, you need to create a measure to look like this:

TotalShipmentPlanning = 
CALCULATE(
    SUM('Shipment Planning'[Quantity]),
    FILTER(
        'Shipment Planning',
        'Shipment Planning'[Item] = SELECTEDVALUE('Order Lines'[Item]) &&
        'Shipment Planning'[Location] = SELECTEDVALUE('Order Lines'[Location])
    )
)

Expected Output:

Please see attached file

[parry2k]

@Olwin BTW, here is the output of the solution.

[parry2k]

@Olwin see the attached solution, in nutshell, you need the following two measures:

Planning Qty = SUM ( ShipmentPlanning[Quantity] )

Total Shipment Planning = 
SUMX ( 
    SUMMARIZE (     
        OrderLines,
        'Item'[Items],
        Location[Location],
        "@Planning Qty", [Planning Qty]
    ), 
    [@Planning Qty] 
)