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

     

     

     

johnt75's avatar
johnt75
Super User
1 year ago

If you have a one-to-many relationship from 'Table A' to 'Table 2' you can create a measure like

Total Cost = SUMX(
    'Table A',
    VAR Volume = SUMX(
        CALCULATETABLE(DISTINCT('Table 2'[Vol])),
        'Table 2'[Vol]
    )
    VAR Result = 'Table A'[Cost] * Volume
    RETURN
        Result
)