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

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

I can think of two ways to do this, one would be to have 5 columns, one for each currency but that has major drawbacks and would likely only work in a table kind of format, maybe some of the other visualizations.

 

I can think of another way potentially, but I need an example of your data to see if it would work. Basically, do you have one value for the currency number and if so, what is the base currency for that? The basic idea would be to create a measure that multipled your currency number by the SUM of all of the entries in your CurrencyConversion table, a table with the conversion rates. This is what the slicer would be based on and when the user selected one of the currencies, the measure would only sum one value. So, unlike a traditional slicer that is related to other data, your CurrencyConversion slicer would not be which is tricky but should work beautiful for your purposes.