Forum Discussion

Olwin's avatar
Olwin
Frequent Visitor
1 year ago
Solved

Sum from another table

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 NoItemLocationQuantity
SP#01X1A10
SP#02X2A20
SP#03X1B30
SP#04X1A17

 

Order Lines

Order NoItemLocationTotal Shipment Planning
SO#01X1A27
SO#02X2A20
SO#03X1A27
SO#04X1B30
Need Help
  • speedramps's avatar
    speedramps
    1 year ago

    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.

     

     

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

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

     

     

     

     

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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

  • speedramps's avatar
    speedramps
    Super User
    1 year ago

    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's avatar
      Olwin
      Frequent Visitor
      1 year ago

      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's avatar
        speedramps
        Super User
        1 year ago

        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!