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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.