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

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

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'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