Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need help changing currency

Hi! I need help converting my sales values between 3 difference currencies. I have 2 slicers in my dashboard, one to select the country that my store is in, and the other to select the currency that I want to view my sales figures in. 

I want to switch between SGD, MYR and USD. I have the conversion rate in the table below. My sales figures are reported in their base currency (Singapore Sales in SGD while Malaysia Sales in MYR). 

Scenarios
Scenario 1:
Country selected = Singapore
Currency selected = SGD/MYR/USD
return total sales in SGD/MYR/USD 

Scenario 2:
Country selected = Malaysia
Currency selected = SGD/MYR/USD
return total sales in SGD/MYR/USD

I have been experimenting with SWITCH() based on SELECTEDVALUE() of my currency slicer but I did not get it to work for all the conversions. Could anyone point me in the right direction? 

Thanks!

Example

Converting total Singapore sales from SGD to USD:

100 + 200 = SGD$300
SGD$300 / 1.349 = USD$222.39

Sample Data


Currency Conversion Lookup:

Conversion RateCountryBase CurrencyConverted CurrencyConversion (notes)
1.349SingaporeSGDUSDSGD to USD
4.578MalaysiaMYRUSDMYR to USD

 

Sales Table: 

CountrySales
Singapore100
Singapore200
Malaysia300
Malaysia600
Conversion
Countries
currency conversion
Multiple Currency
sales
  • ERD's avatar
    ERD
    2 years ago

    Hi Anonymous , one of the ways to achieve the result:

    amt =
VAR selectedCurrency = SELECTEDVALUE ( Currencies[Currency] )
VAR rateToUSD =
    IF (
        selectedCurrency = "USD",
        1,
        CALCULATE (
            MAX ( 'Currency Conversion'[Conversion Rate] ),
            'Currency Conversion'[Base Currency] = selectedCurrency,
            ALL ( 'Currency Conversion' )
        )
    )
VAR selectedCountry = SELECTEDVALUE ( 'Currency Conversion'[Country] )
VAR selectedCountryCurrency =
    CALCULATE (
        MAX ( 'Currency Conversion'[Base Currency] ),
        'Currency Conversion'[Country] = selectedCountry,
        ALL ( 'Currency Conversion' )
    )
VAR selectedCountryCurrencyRate =
    CALCULATE (
        MAX ( 'Currency Conversion'[Conversion Rate] ),
        'Currency Conversion'[Base Currency] = selectedCountryCurrency,
        ALL ( 'Currency Conversion' )
    )
VAR valueUSD = [Sales amt] / selectedCountryCurrencyRate
RETURN
    IF (
        ISFILTERED ( Currencies[Currency] )
            && ISFILTERED ( 'Currency Conversion'[Country] ),
        IF ( selectedCurrency = "USD", valueUSD, valueUSD * rateToUSD )
    )

     

1 Reply

  • ERD's avatar
    ERD
    Community Champion
    2 years ago

    Hi Anonymous , one of the ways to achieve the result:

    amt =
VAR selectedCurrency = SELECTEDVALUE ( Currencies[Currency] )
VAR rateToUSD =
    IF (
        selectedCurrency = "USD",
        1,
        CALCULATE (
            MAX ( 'Currency Conversion'[Conversion Rate] ),
            'Currency Conversion'[Base Currency] = selectedCurrency,
            ALL ( 'Currency Conversion' )
        )
    )
VAR selectedCountry = SELECTEDVALUE ( 'Currency Conversion'[Country] )
VAR selectedCountryCurrency =
    CALCULATE (
        MAX ( 'Currency Conversion'[Base Currency] ),
        'Currency Conversion'[Country] = selectedCountry,
        ALL ( 'Currency Conversion' )
    )
VAR selectedCountryCurrencyRate =
    CALCULATE (
        MAX ( 'Currency Conversion'[Conversion Rate] ),
        'Currency Conversion'[Base Currency] = selectedCountryCurrency,
        ALL ( 'Currency Conversion' )
    )
VAR valueUSD = [Sales amt] / selectedCountryCurrencyRate
RETURN
    IF (
        ISFILTERED ( Currencies[Currency] )
            && ISFILTERED ( 'Currency Conversion'[Country] ),
        IF ( selectedCurrency = "USD", valueUSD, valueUSD * rateToUSD )
    )