cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Employee

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
Employee

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
Employee

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

Employee

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### 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 Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors