cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jpapador Regular Visitor
Regular 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

Accepted Solutions
v-micsh-msft New Contributor
New Contributor

Re: Control which currency is displayed with slicer

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:

23.PNG24.PNG

25.PNG26.PNG

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

Regards

 

13 REPLIES 13
Super User
Super User

Re: Control which currency is displayed with slicer

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. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


CahabaData New Contributor
New Contributor

Re: Control which currency is displayed with slicer

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.

 

www.CahabaData.com

Re: Control which currency is displayed with slicer

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
v-micsh-msft New Contributor
New Contributor

Re: Control which currency is displayed with slicer

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:

23.PNG24.PNG

25.PNG26.PNG

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

Regards

 

MrPowerBIPro Regular Visitor
Regular Visitor

Re: Control which currency is displayed with slicer

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
MrPowerBIPro Regular Visitor
Regular Visitor

Re: Control which currency is displayed with slicer

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
Highlighted
MrPowerBIPro Regular Visitor
Regular Visitor

Re: Control which currency is displayed with slicer

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
JorisVoorn Frequent Visitor
Frequent Visitor

Re: Control which currency is displayed with slicer

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

valtuille Visitor
Visitor

Re: Control which currency is displayed with slicer

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?