Forum Discussion

kevbrown1980's avatar
kevbrown1980
Frequent Visitor
1 year ago
Solved

Calculations

Hi   I have 2 table with joined, 1 table has my cost data and the other the volume data. The cost table is 1 line per team however the volume table is multiple row per department giving an overal v...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi kevbrown1980 

     

    If the code in Table A is always unique, you can create a one-to-many single direction relationship from Table A to B on code. Then create this measure (assuming there's only one distinct vol value per team regardless of the department):

    total cost = 
SUMX (
    VALUES ( TableA[Code] ),
    CALCULATE ( MAX ( TableB[Vol] ) ) * CALCULATE ( SUM ( TableA[Cost] ) )
)

     

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
11 months ago

Hi,

You may write these calculated column formulas in Table1

Volume = calculate(min('Table2'[Vol]),filter('Table2','Table2'[Code]=earlier('Table1'[Code])))

Total = 'Table1'[Cost]*'Table1'[Volume]

Hope this helps.