Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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
1 ACCEPTED SOLUTION
ERD
Super User
Super User

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

ERD_0-1695193395580.png

ERD_1-1695193412288.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

1 REPLY 1
ERD
Super User
Super User

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

ERD_0-1695193395580.png

ERD_1-1695193412288.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors