Control which currency is displayed with slicer

[jpapador]

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.

[MrPowerBIPro]

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

[JorisVoorn]

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

[MrPowerBIPro]

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

[MrPowerBIPro]

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

[v-micsh-msft · Accepted solution]

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]

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]

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]

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

[JorisVoorn]

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

[pgarcia]

Hi JorisVoorn

Thanks by your reply.

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

[a_mixed_life]

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.

[CahabaData]

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.

[Greg_Deckler]

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.