Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiple Currency conversion to USD Amount

Hi all,

 

Request your assistance with DAX measure.

 

Data table:

Value columns show data in multiple currencies namely AUS & SGP. (Since this is sample data – the actual data will have other country data as well in their respective local currencies).

There is a column called “Country” that shows local currency data.

 

ExchRate table:

Country column in this table is same as Country column in ‘Data’ table.

 

CCY table:

Table storing info on USD’000s and USD Mns.

 

Below is what I am trying to achieve:

  1. Give the report user an option to look at the report in USD ‘000s or in USD Millions.
  2. DAX measure that calculates sum total of a particular column (Value1, Value 2, Value 3, or a combination of any of these columns) converted into USD amounts.
    1. If user has not selected any of the option (i.e., in ‘000s or Mns’) the report should default to show numbers in USD’000s.
    2. The DAX measure should work with Matrix table and any other visuals.

 

PBIX file can be downloaded from here: https://www.dropbox.com/s/3dvsntjbaepvczi/Multiple%20CCY%20Test.pbix?dl=0

In the PBIX file I have provided the desired result as well in a text box.

 

Thank you in advance for your help.

 

-Deepak.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Please try the measure 

     

    Testing_3 =
VAR Selection =
    SELECTEDVALUE ( CCY[CCY] )
VAR SelCountry =
    SELECTEDVALUE ( Data[Country] )
VAR ConvertedAmount =
    SUMX (
        Data,
         ( Data[Value 1] + Data[Value 2] + Data[Value 3] )
            / RELATED ( ExchRate[Exch Rate] )
    )
RETURN
    IF (
        ISBLANK ( Selection ),
        ConvertedAmount / 1000,
        IF (
            Selection = "US$ in '000",
             ( ( ConvertedAmount ) / 1000 ),
            ConvertedAmount / 1000000
        )
    )

     

    If this works for you, please accept this as solution and also give Kudos.

     

    Cheers

     

    CheenuSing

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Deepak,

     

    I made changes to the Test1 Dax as under

    Testing_1 = Var Selection = SELECTEDVALUE(CCY[CCY])
    Var SelCountry = SELECTEDVALUE(Data[Country])
    Var Amount = (sum(Data[Value 1]) + sum(Data[Value 2]))
    Var RateS = MAXX(FILTER(ExchRate,[Country]=SelCountry),ExchRate[Exch Rate])
    Var ConvertedAmount = Amount/RateS
    Return
    IF(ISBLANK(Selection),ConvertedAmount/1000,
    IF(Selection="US$ in '000",((ConvertedAmount)/1000),
    ConvertedAmount/1000000))
     
    You cannot select all the countries from the slicer, only one country can be selected.
     
    Let me know if this works
     
    Cheers
     
    CheenuSing
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi CheenuSingh,

       

      Thank you so much for your prompt reply.

      This solution works partially. I would like to have a measure that allows the user to select multiple countries and the result should show consolidated amount in USD.

       

      Reason: Users of the report are responsible for more than one country and they would like to have a report that shows them at a glance total value across different countries in USD amount (after applying the correct exchange rates). 

       

      Appreciate your assistance.

      -Deepak. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi all,

    Appreciate if anyone could help me with the DAX measure that allows the user to select multiple countries and the result should show consolidated amount in USD.

     

    Thanks again.

    Deepak.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi all,

    Appreciate if anyone could help me with the DAX measure that allows the user to select multiple countries and the result should show consolidated amount in USD.

    Thanks again.

    Deepak.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

       

      Please try the measure 

       

      Testing_3 =
VAR Selection =
    SELECTEDVALUE ( CCY[CCY] )
VAR SelCountry =
    SELECTEDVALUE ( Data[Country] )
VAR ConvertedAmount =
    SUMX (
        Data,
         ( Data[Value 1] + Data[Value 2] + Data[Value 3] )
            / RELATED ( ExchRate[Exch Rate] )
    )
RETURN
    IF (
        ISBLANK ( Selection ),
        ConvertedAmount / 1000,
        IF (
            Selection = "US$ in '000",
             ( ( ConvertedAmount ) / 1000 ),
            ConvertedAmount / 1000000
        )
    )

       

      If this works for you, please accept this as solution and also give Kudos.

       

      Cheers

       

      CheenuSing

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thank you so much Anonymous

        This was exactly what I was looking for.

         

        Thanks once again for your prompt help.

         

        Regards,

        Deepak.