Forum Discussion

javigold's avatar
javigold
Frequent Visitor
3 years ago
Solved

Dynamic calculated column

I am trying to normalize my data to USD.   I want to create a calculated column on my data table and I have an FX data table populated with all the exchange rates I need.   

 

My Fx  Table:

 

 

My Data Table:

 

 

I aim to add a column to the data table with the calculated amount based on the exchange rate taken from the Fx table.   Thoughts on the best approach?

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi javigold ,

    If I understand correctly, you would like to get a calculated column which can retrieve the exchange rate from Fx table. But it need to keep the current table structure of Fx table. Am I right? If yes, you can create a calculated column as below to get it, please find the details in the attachment.

    Exchange Rate = 
SWITCH (
    'Data'[CurrencyCode],
    "AUD",
        CALCULATE (
            MAX ( 'Fx'[AUD] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "CHF",
        CALCULATE (
            MAX ( 'Fx'[CHF] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "EUR",
        CALCULATE (
            MAX ( 'Fx'[EUR] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "GBP",
        CALCULATE (
            MAX ( 'Fx'[GBP] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "INR",
        CALCULATE (
            MAX ( 'Fx'[INR] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "JPY",
        CALCULATE (
            MAX ( 'Fx'[JPY] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "USD",
        CALCULATE (
            MAX ( 'Fx'[USD] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        )
)

    Best Regards

4 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    3 years ago

    Hello,

     

    First, go to power Query and merge the table fx to the data table were activity date = Date.

    then when you expand the table, just take the column USD column to your data table.

    Then create a custom column where you calculate the amount based on the exchange rate based on the equation.

    finally you'll have a new amount column that you can use

     

    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

    Follow me on Linkedin

    • javigold's avatar
      javigold
      Frequent Visitor
      3 years ago

      I don't want to merge the tables as I want to keep expanding the fx table and use it against other data tables that I bring in.   My main issue is how do I use the currency value in my data table as a variable to pull from the correct column in the fx table.    

      • Idrissshatila's avatar
        Idrissshatila
        Super User
        3 years ago

        Then add a calculated column in the data table and use the dax function called lookup value to get the value from the column needed from the fx table.

         

        Check this link for the lookup value function  https://dax.guide/lookupvalue/

        If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

        Follow me on Linkedin

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi javigold ,

    If I understand correctly, you would like to get a calculated column which can retrieve the exchange rate from Fx table. But it need to keep the current table structure of Fx table. Am I right? If yes, you can create a calculated column as below to get it, please find the details in the attachment.

    Exchange Rate = 
SWITCH (
    'Data'[CurrencyCode],
    "AUD",
        CALCULATE (
            MAX ( 'Fx'[AUD] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "CHF",
        CALCULATE (
            MAX ( 'Fx'[CHF] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "EUR",
        CALCULATE (
            MAX ( 'Fx'[EUR] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "GBP",
        CALCULATE (
            MAX ( 'Fx'[GBP] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "INR",
        CALCULATE (
            MAX ( 'Fx'[INR] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "JPY",
        CALCULATE (
            MAX ( 'Fx'[JPY] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        ),
    "USD",
        CALCULATE (
            MAX ( 'Fx'[USD] ),
            FILTER ( 'Fx', 'Fx'[Date] = 'Data'[ActivityDate] )
        )
)

    Best Regards