Forum Discussion
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 have a column named Hours
In table T_Part I have a column named Batchqty
What I would like is to create a measure which: Divides the value in QT by Batchqty and this result should be multy the value of Hours
For example:
PartCode X = (5000/1000)*3 = 15
PartCode x= (Qty/Batchqty)*Hours
Since it has multiple tables I am for some reason stuck. Can someone give me directions?
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 )
4 Replies
- Arul
Super User
Could you provide atleast three rows of sample data for all 3 tables?
Aantal = Qty
CalcComputQty = BatchQty
Aantal = Qty
- Arul
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 )
