Using Alternative exchange rate

[Ashishvca]

I want to use four columns in my dataset : 

- values in Local currency

- Budget Rates

- Actual/Forecast Rates

- Selected Rates

'Selected Rates' should show Either 'Budget Rates' or 'Actual Rates', based on my selection on the Power BI report to switch the values between the two Fx rates. How do I achieve that?

Thanks in advance

[Ashishvca]

I did not do a good job in the first go to explain the problem I have so here I am elaborating the problem further

- values in Local currency

- Budget Rates =  RELATED(Rates[2022 Budget FX Rate])

- Actual/Forecast Rates = RELATED(Fx[Actual Fx]) This column has monthly exchange rates for 39 currencies for 2 years

- Selected Rates = *chose between budget and actual rates - to be solved*

- USD = (values in Local currency)/Selected Rates

As you said, having multiple rates in the main data table leads to having redundant data and it does lead to an increase in size, especially when my data table has 17.9 million rows. I intend to remove 'actual/Forecast rates' and 'budget rates' from data table once I have selected rates formula identified.

I need to have USD in my data table as it is the central column driving all my reports. Hence I need to have 'Selected rates' to be included in the data table without making drastic changes to the report structure. 

[FreemanZ]

hi @Ashishvca 

Keeping the rates in the fact table gives too much duplicates.

In practice, it is more like this:

supposing you have two tables like this:

data table(this could be a long table):

rate table(this is typically a short table):

the two tables are unrelated. 

then 

1）write a measure like this:

NewValue = SUMX(data, data[Value]*SELECTEDVALUE(Rates[Rate]))

2）plot a slicer with rates[rateselection] column and other visual with the measure.