cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 5 currency types, a user could select one of the currencies from the list (IE USD) then all the charts would display USD.

Is this possible?  The basic thought is controlling a variable using a slicer value.

1 ACCEPTED SOLUTION
Microsoft

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

13 REPLIES 13
Helper II
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
Enemy of can't
www.mrpowerbi.pro
Microsoft

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.

Best regards

Pavel

Helper II
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
Enemy of can't
www.mrpowerbi.pro
Helper II

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 ?

Regards

Mostafa

Enemy of can't
www.mrpowerbi.pro
Microsoft

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

Regular Visitor

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
Not applicable

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.

Frequent Visitor

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

Microsoft

Hi pgarcia,

I guess you did it via If function in Measure, it would help if you include FORMAT function https://msdn.microsoft.com/en-us/query-bi/dax/format-function-dax.

Unfortunately that is not solution for me :).

BR

Pavel

Frequent Visitor

Hi JorisVoorn

I've built the measure with format. My measure is:

Measure Select = SWITCH( true(); VALUES( 'Dim Currency'[CurrencyDesc] )= "EUR" ; format([Value_EUR];" #,### €");
VALUES('Dim Currency'[CurrencyDesc] )= "USD" ;format([Value_USD];"#,### \$"))

Best regards

Resolver I

Depending on data that you may have available, you could create an ExchangeRateCurrency table. Some ERP have Currency table that holds the conversion that you could pull then use the slicer against the 'Code' or 'Symbol'. That way it will convert the value base on the currency you chose on your slicer.

Kris
Memorable Member

I believe you must have a field/column in your table that states currency type, in order to slice/filter on it.

How that is achieved would depend on what indicator you can be sure of within the other data fields.

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors