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 possbile?

 

Thanks

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

7 Replies

  • Thejeswar's avatar
    Thejeswar
    Super User
    4 years ago

    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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • 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,