Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

dax for conversion rate

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 ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Thejeswar's avatar
Thejeswar
Super User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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