Currency Conversion of data in different currencies

[Anonymous]

I currently have a table which comes in the format as shown in the table below (Raw data image). The UK data in represented in GBP and the data from Ireland is represented in EUR.  I would like to create a currency measure which converts all of the data into one currency. i.e either GBP OR EUR in one column. The results image below shows the actual results based on the currency. I tried to create a  measure (currency measure image) based on the dates and currency selected to covert my raw data into either currency,  however it does not seem to work. Can anyone assist or advise on the best approach for this?

Raw data TableResults TableCurrency MeasureExchange Rate used

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

According to my understand, you want to calculate the Actuals * Rate based on selected value in Slicer, right?
You could use the following formula:

Measure =
SWITCH (
    MAX ( 'Table'[Country] ),
    "Ireland",
        CALCULATE ( MAX ( 'Rate'[Rate] ), 'Rate'[Repo] = "GBP" )
            * MAX ( 'Table'[Actuals] ),
    "UK",
        CALCULATE ( MAX ( 'Rate'[Rate] ), 'Rate'[Repo] = "EUR" )
            * MAX ( 'Table'[Actuals] )
)

Measure 2 = 
var _sele=SELECTEDVALUE('Rate'[Repo])
var _rate=CALCULATE(MAX('Rate'[Rate]),'Rate'[Repo]=_sele)
var _country=IF(_sele="GBP","Ireland",IF(_sele="EUR","UK"))
return CALCULATE(MAX('Table'[Actuals]),FILTER('Table','Table'[Country]=_country)) *_rate 

Measure 3 =
IF ( [Measure 2] = BLANK (), MAX ( 'Table'[Actuals] ), [Measure 2] )

The different visualizations look like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[lbendlin]

Please be more specific. What have you tried and where are you stuck?

[Anonymous]

Essenitally i would like to create a measure in which if EUROs is selected then you would only convert the UK actuals value into Euros and if GBP is selected you would only convert the Ireland actuals value into pounds.  

[lbendlin]

" if EUROs is selected " - How? In a disconnected slicer?