Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jpapador
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
v-micsh-msft
Employee
Employee

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

 

View solution in original post

13 REPLIES 13
MrPowerBIPro
Helper II
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

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
Helper II
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
MrPowerBIPro
Helper II
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
v-micsh-msft
Employee
Employee

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

 

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.

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.

 

 Captura.PNG

 

How could I sort descending the values?

 

Many thanks.

 

Best regards

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

 

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
Resolver I
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
CahabaData
Memorable Member
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.

 

www.CahabaData.com
Greg_Deckler
Super User
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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.