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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors