Multiplication with lookup()

[Anonymous]

I have a fact table as below
Curreny  Amount

usd          100

eur           75
cny           50

slicertable

Curreny  rate_1    rate_2    rate_3

usd          1            1            1

eur           0.75      0.85         0.95
cny           0.50      0.65         0.80 

rate_1_measure = 100*1 + 75*0.75 + 50*0.50

what I am trying is 

rate_1_measure  =

PRODUCTX(facttable[Amount],

LOOKUPVALUE(slicertable[rate_1],

                        slicertable[Currency],  facttable[Currency]))

How can I create this meaure correctly in dax ?

[PijushRoy · Accepted solution]

Hi @Anonymous 

Please try below DAX in measure

Rate_1_Equivalent = 
VAR CurrencyAmounts = SUMMARIZE('Facttable', facttable[Curreny], 'Facttable'[Amount])
VAR USD_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "usd"), 'slicertable'[rate_1])
VAR EUR_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "eur"), 'slicertable'[rate_1])
VAR CNY_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "cny"), 'slicertable'[rate_1])

RETURN
SUMX(
    CurrencyAmounts,
    SWITCH(
        facttable[Curreny],
        "usd", 'Facttable'[Amount] * USD_Rate_1,
        "eur", 'Facttable'[Amount] * EUR_Rate_1,
        "cny", 'Facttable'[Amount] * CNY_Rate_1
    )
)

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Anonymous]

thank you very much.

[PijushRoy]

Hi @Anonymous 

If it is working, please mark my PREVIOUS comment as SOLUTION