Need help changing currency

Anonymous — Wed, 20 Sep 2023 04:25:35 GMT

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 Rate	Country	Base Currency	Converted Currency	Conversion (notes)
1.349	Singapore	SGD	USD	SGD to USD
4.578	Malaysia	MYR	USD	MYR to USD

Sales Table:

Country	Sales
Singapore	100
Singapore	200
Malaysia	300
Malaysia	600

Re: Need help changing currency

ERD — Wed, 20 Sep 2023 07:03:40 GMT

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

topic Need help changing currency in DAX Commands and Tips

Need help changing currency

Re: Need help changing currency