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

     

v-micsh-msft's avatar
v-micsh-msft
Icon for Microsoft Employee rankMicrosoft Employee
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

 

valtuille's avatar
valtuille
Regular Visitor
8 years ago

How could I use a DAX expression if I have the rates for different years?

 

I could only think on concatenating the Year with the Currency, however, I have the year as key to other table.

 

 

Any suggestion?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thanks a lot for the replies. 

     

    But I am strill struggeling with one issue:

     

    I am converting my Dollars in Euros and Yen. That works fine and I have a column with all transactions in the required currency.

     

    But since it is a measure, I can not use any aggregations such as AVG on a measure.

     

    The customer wants to see all his sales in the required column - as SUM or as AVGs. He wants to be able to do it on his own.


    Any suggestions ? I don't want to have to separate measures.

    • pgarcia's avatar
      pgarcia
      Frequent Visitor
      8 years ago

      Hi all!

       

      I have a currency slicer, which I select a currency and show me the conversion in Dollars or Euros in  a matrix.

       

      Now, I want to sort descending by the value of currency, but the measure created have the function format, so it's a text and the sort is wrong.

       

       

       

      How could I sort descending the values?

       

      Many thanks.

       

      Best regards