filter, multiply, and sum in dax

Question

I have 2 tables as below, i would like to filter both table, multiply with the relevant rate, and sum the amount. Many thanks in advance.

_factFX table should be filtered by FY24

Rate  Currency  Fiscal Year
0.5     CNY       FY24
0.75    CAN       FY23
1       USD       FY24
1       USD       FY23
1.3     EUR       FY24
1.5     EUR       FY23

_factInput table should be filtered by A

Currency   Amount  A/B
EU         10       A
USD        10       A
CNY        10       A
USD        10       B
CNY        10       B

Expected Answer

10*1.3 + 10*1 + 10*0.5

Answer 1

Assuming no relationships and that EU = EUR in your test data, then the following works. It is a very strange requirement though.

Measure = 
CALCULATE(
    SUMX(_factInput, 
        VAR x = CALCULATE(MAX(_factInput[Currency]))
        VAR r = CALCULATE(MAX(_factFX[Rate]), _factFX[Currency] = x, _factFX[Fiscal Year] = "FY24")
        RETURN
        _factInput[Amount] * r
    )
, _factInput[A/B] = "A"
)