dax for conversion rate

[Anonymous]

Hi,


I have a report with two different currencies, one is GBP(£) and the other is Euros. 
However when combined on power bi I want the total to be SEK = Swedish krona.

How would this be possbile?

Thanks

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, I create a sample.

Here's my solution, create a measure.

Measure =
SUMX (
    'Table',
    IF (
        'Table'[Country] = "UK",
        'Table'[Currency] * 246.54,
        IF ( 'Table'[Country] = "Ireland", 'Table'[Currency] * 308.37 )
    )
)

Whether the slicer is single-choice or multiple-choice, it will get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi 

I've made a change to the formula by multiplying the country by the conversion rate for SEK however I need to include in the measurement 
If the one country is selected keep it as the value in its own currency before multiplying by the conversion rate.
for example if its £200 and £300 for uk only the total will be £500 but if both uk and ireland are selected that when it changes to the totaled value multiplied by conversion rate.


e.g

if 'uk' its normal = £500 
if 'ireland' then normal = 600 
but if its both then 
uk and ireland = uk * sek conevrsion rate + ireland * sek conversion rate 

[Thejeswar]

Hi @Anonymous ,

You need to create a new measure as like given below. Also for this to be achieved, I created one more table where I am maintaining the conversion rates along with the Country column

Measure_new = 
var selected_country = IF(ISFILTERED('Table'[Country]), CONCATENATEX(CALCULATETABLE(VALUES('Table'[Country])), 'Table'[Country], ","), "")
return
IF(COUNTROWS(ALLSELECTED('Table'[Country])) > 1, SUMX('Table', 'Table'[Currency] * RELATED(currency_conversion[Conversion_rate])),
IF(selected_country = "UK" , SUM('Table'[Currency]), SUM('Table'[Currency])
))

New table and how it is related to the country table:

When one country is selected:

When both the countries are selected

If this works fine, mark it as answer and I would love to be appreciated with a Kudo!!

Best Regards,

[Anonymous]

Hi that looks like its working for you 

I've done this

SEK =

var selected_country = IF(ISFILTERED('Pipeline'[Country Name]), CONCATENATEX(CALCULATETABLE(VALUES('Pipeline'[Country Name])), 'Pipeline'[Country Name], ","), "")

return

IF(COUNTROWS(ALLSELECTED('Pipeline'[Country Name])) > 1, SUMX('Pipeline', 'Pipeline'[Financed Amount] * RELATED('Sheet1'[conversion_rate])),

IF(selected_country = "UK" , SUM('Pipeline'[Financed Amount]), SUM('Pipeline'[Financed Amount])

))

but when i click one country it still doesnt show me the value with out it being multiplied by conversion rate so the original number isnt shown

[v-yanjiang-msft]

Hi @Anonymous ,

@Thejeswar 's formula works in my sample, you can compare the difference of my sample and yours, I attach it below. Analyzing where things went wrong can solve problems faster, or you can provide your sample without sensitive data, to let me know the difference.

Best Regards,
Community Support Team _ kalyj

[Anonymous]

By combine I mean when my report slicer is selecting UK and Ireland which have different currencies how can I create an automatic dax measure which converts the two into SEK and adds them together 

20 GBP 
30 EU

246.54 - UK TO SEK
308.37 - EU TO SEK 

TOTAL COMBINED = 554.91

[Thejeswar]

Hi @Anonymous , 

What do you mean when you say combine?

It would be better if you can give  sample data and expected output. Also explain based on what you combine GBP and Euros?

Regrads,