Dynamic calculated column

[javigold]

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!

[Anonymous · Accepted solution]

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

[Idrissshatila]

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]

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]

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