Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Multiplication with lookup()

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 ?
Need Help
  • PijushRoy's avatar
    PijushRoy
    2 years ago

    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

3 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      thank you very much.

       

      • PijushRoy's avatar
        PijushRoy
        Community Champion
        2 years ago

        Hi Anonymous 

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