Forum Discussion

ddijstelbloem's avatar
ddijstelbloem
Frequent Visitor
2 years ago
Solved

Making a calculation based on data in three related tables

I have 3 different tables:   T_Part T_FC T_BillofOper   T_FC and T_BillofOper are both related to T_Part by column PartCode   In table T_FC I have a column named Qty In table T_BillofOper I ...
Need Help
  • Arul's avatar
    Arul
    2 years ago

    ddijstelbloem ,

    Check whether this measure is helping or not,

    PartCode X = 
VAR _tempTable =
    SUMMARIZE (
        T_Part,
        T_Part[PartCode],
        "@QTY", SUM ( T_FC[Aantal] ),
        "@BatchQty", SUM ( T_Part[CalComputQty] ),
        "@Hrs", SUM ( T_BillofOper[LineNr] )
    )
VAR _result =
    SUMX ( _tempTable, DIVIDE ( [@QTY], [@BatchQty] ) * [@Hrs] )
RETURN
    IF ( ISBLANK ( _result ), 0, _result )
Arul's avatar
Arul
Super User
2 years ago

ddijstelbloem ,

Could you provide atleast three rows of sample data for all 3 tables?

ddijstelbloem's avatar
ddijstelbloem
Frequent Visitor
2 years ago

Aantal = Qty

CalcComputQty = BatchQty

Aantal = Qty

  • Arul's avatar
    Arul
    Super User
    2 years ago

    ddijstelbloem ,

    Check whether this measure is helping or not,

    PartCode X = 
VAR _tempTable =
    SUMMARIZE (
        T_Part,
        T_Part[PartCode],
        "@QTY", SUM ( T_FC[Aantal] ),
        "@BatchQty", SUM ( T_Part[CalComputQty] ),
        "@Hrs", SUM ( T_BillofOper[LineNr] )
    )
VAR _result =
    SUMX ( _tempTable, DIVIDE ( [@QTY], [@BatchQty] ) * [@Hrs] )
RETURN
    IF ( ISBLANK ( _result ), 0, _result )
    • ddijstelbloem's avatar
      ddijstelbloem
      Frequent Visitor
      2 years ago

      Thanks! After some small adjustments with the right colums it works!