Variable for currency exchange rate

[John_SM]

Hi there experts, I would need your help. I found similar posts about this but didnt quite get it.

I have a table with the following columns: Date, Sales(USD), EUR, GBP, CHF.  Date is the day date, Sales(USD) is the value of sales for that day in US Dollars, and the rest of the columns contain the exchange rate for that currency per day. 
I want to create a filter so that the user can select the currency and the expression will change based on the selection.
I created a variable and assigned it the column name but its not working.
For example when a user selects EUR the calculation sum(Sales) would change to sum(Sales*vCurrency) where vCurrency would be the variable name containing EUR, GBP, CHF. I created in the front end a variable vCurrency and with the extension Variable Input I assigned it different values (Label= Euro, Value= EUR and same for all the other currencies)

Date Sales(USD) EUR GBP CHF

01.01.2020	100	0.85	0.95	..
02.01.2020	200	0.84	..	..
03.01.2020	300	0.87	..	..
…	…

[Almen]

Create a new table  in your script:

ExchangeType:
LOAD * INLINE [
Order, ExchangeType
1,EUR
2,GBP
3,CHF
4,...
];

Create Filter in your dashboard, use ExchangeType.

Set the field to always one value picked in field properties.

Create the variable vCurrency and type:

=GetFieldSelections(ExchangeType)

Final measure in your table:

pick(
match($(vCurrency),'EUR','GBP','CHF',...),
Sales(USD)*EUR,
Sales(USD)*GBP,
Sales(USD)*CHF,
...
)

[John_SM]

Hi @Almen , thank you for your reply. What I am trying to avoid is creating a huge script in the expression. In the backend I created new columns in that table like: Sales (EUR) = Sales (USD)*EUR and so on for all the currencies and in the frontend I already made a variable vCurrency and gave it the values 1 to 3 (I actually have more currencies but lets take only 3 for now) and made the expression: if vCurrency=1 then Sales (EUR), if vCurrency=2 ... and so on, where the user can select the currency from a filter created with variable input extension. The problem is that I have a lot of calculation that I need to do in the frontend (for some reasons not possible in the backend) and I am trying to avoid creating a lot of "If". I would prefer if possible instead of "if vCurrency=1 then Sales (EUR), if vCurrency=2 ..." to have "Sales (USD)*vCurrency" so only one line for all currencies. Basically I am trying to store in a variable an entire column if that makes sense..

[Almen]

If you already created Sales fields in your backend for all the different currencies, you can add them to your table and use your vCurrency in Show Column when  like $(vCurrency)= 1 for EU, $(vCurrency)=2 for GPB and so on.

This way only one column would be calculated at any given time.

[John_SM]

Thank you @Almen , I thought about this option also, but I want to avoid creating so many columns (one column for each currency). What I want is a single column that would calculate the values dynamically depending on the user selection

[John_SM]

Any other help?