Forum Discussion

jpapador's avatar
jpapador
Frequent Visitor
10 years ago
Solved

Control which currency is displayed with slicer

I have a list of 5 possible currencies that users may want to see.  Is there a way with a slicer to be able to control whih currency amount is displayed.  The idea would be that a slicer contains the...
Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    10 years ago

    Hi jpapador,

    Yes, this is possible. Let’s take an example to explain how it would work:

    First we need to build a new table named as Currency as below:

    CurrencyName

    Rate

    Symbol

    US dollar

    1

    $

    Euro

    0.88

    €

    RMB/CNY

    6.65

    ¥

    Russian Ruble

    64.88

    ₽

     

    Here we take use of US dollar as the basic currency rate, CurrencyName in Currency table as the Slicer option:

    Then under each table, when using columns with currency(Sheet2[sales] as an example here), create a new measure in the format below:

    Currentsales = IF(

                             ISFILTERED('Currency'[CurrencyName]),

                             sum(Sheet2[sales])*VALUES('Currency'[Rate]),

                             sum(Sheet2[sales])

                                )

    And if you would like to show the Currency Symbol, write another measure as below:

    CurrentSymbol = IF(

                                ISFILTERED('Currency'[CurrencyName]),

                                values('Currency'[Symbol]),

                                CALCULATE(values('Currency'[Symbol]), 'Currency'[Rate]=1)

                                 )

    See the result under Power BI desktop:

    If any further help needed, please feel free to post back.

    Regards

     

MrPowerBIPro's avatar
MrPowerBIPro
Icon for Advocate II rankAdvocate II
9 years ago
For currency conversion, I build a Slicer for change the currency. Then I wrote a formula to change the curreny: For example suppose defualt currecy is Euro: Euro_rate1= 1.05* US Dollar Euro_rate2 =3378 * Irianian Rial (IRR) IF(VALUES(Currency_Table[CurrencyType])="Dollar",FORMAT(SUM(Sales [Euro])/[Euro_rate1]),"$ #,##0.00;($ #,##0.00)") ,IF(VALUES(Currency_Table[CurrencyType])="IRR",FORMAT(SUM(Incentive_Table[Euro])/[Euro_rate2]),"€ #,##0.00;(€ #,##0.00)"),FORMAT(SUM(Incentive_Table[Euro])/1),"IRR #,##0.00;(IRR #,##0.00)"))) Means: IF Currency Type="Dollar" Then Sales based on Euro/Euro_rate1 IF Currency Type="IRR" Then Sales based on Euro/Euro_rate2 Then format them. Did you get it ? I hope it is helpful. Regards Mostafa
JorisVoorn's avatar
JorisVoorn
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi guys,

 

The way how to build a slicer to switch currencies is clear. But do you have any idea in case I need to implement also Price Band filters? Imagine that you have products that has different prices in EUR/USD, I need to have there easy switch for currencies that also affects Priceband filters that will be showing everytime same price band (for example <300, 300-500, 500-800, 800+) but as switcher will be set for USD it will be USD, and for EUR -> EUR. 

 

I know that there might be work around with with bookmarks but this is not "one click" solution, that I am looking for.

 

Thanks in advance for ideas.

 

Best regards

 

Pavel