cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KougaGennosuke
New Member

Currency conversion

Hello community,

 

I'm a new Power BI user and would greatly appreciate your help regarding setback I encountered.

 

I'm building a quite simple dashboard consisting of yearly data (year end, for 5 years) for 100 products. I would like to see a single product's data (revenue, yearly logistics cost ect) throughout the years. I'm using slicer to enable selection of single product. Given that this is a pretty straightforward exercise, it was fairly simple to build this. The difficult part refers to the fact that I would like to see (by using another slicer) the same data by product in two additional currencies. So when I click on EURO for example all the charts which were in USD convert to EUR. Also the fact that exchange rate is different at the end of each year does not help 🙂 

 

Thank you very much in advance for any suggestion.

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

Hi @KougaGennosuke ,

 

According to your description, you can refer to the following companies. Create three separate measures corresponding to REVENUES, LOGISTICS COST, and TAX PAID fields. Depending on the choice of currency in the slicer, the corresponding column visual is dynamically displayed.

M_REVENUES =
SWITCH (
    SELECTEDVALUE ( 'FX table'[Currency] ),
    "USD",
        CALCULATE (
            SUM ( 'Table'[REVENUES (EUR)] ),
            ALLEXCEPT ( 'Table', 'Table'[Product name], 'Table'[DATE] )
        ),
    "GBP",
        CALCULATE (
            SUM ( 'Table'[REVENUES (EUR)] ),
            ALLEXCEPT ( 'Table', 'Table'[Product name], 'Table'[DATE] )
        )
)

vhenrykmstf_0-1643164094416.pngvhenrykmstf_1-1643164123458.png


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-henryk-mstf
Community Support
Community Support

Hi @KougaGennosuke ,

 

According to your description, you can refer to the following companies. Create three separate measures corresponding to REVENUES, LOGISTICS COST, and TAX PAID fields. Depending on the choice of currency in the slicer, the corresponding column visual is dynamically displayed.

M_REVENUES =
SWITCH (
    SELECTEDVALUE ( 'FX table'[Currency] ),
    "USD",
        CALCULATE (
            SUM ( 'Table'[REVENUES (EUR)] ),
            ALLEXCEPT ( 'Table', 'Table'[Product name], 'Table'[DATE] )
        ),
    "GBP",
        CALCULATE (
            SUM ( 'Table'[REVENUES (EUR)] ),
            ALLEXCEPT ( 'Table', 'Table'[Product name], 'Table'[DATE] )
        )
)

vhenrykmstf_0-1643164094416.pngvhenrykmstf_1-1643164123458.png


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

parry2k
Super User
Super User

@KougaGennosuke it should have 3 columns:

 

Date, Currency, FX Rate

 

Date is the date of FX Rate

Currency what currency it is EUR -> USD, EUR -> GBP

FX Rate -. Converion Rate 

 

So if you have 2 other currencies you want to see data in, and if we take one day as an example, you will have two record in this table

 

Jan 1/USD/0.90

Jan 1/EBP/1.10

 

I hope this helps

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yes, this is tremendous help, thank you.

 

I created a sample FX table:

 

KougaGennosuke_0-1642872912799.png

And made a connection between dates:

KougaGennosuke_1-1642872981128.png

Would you be so kind to suggest DAX formula (or some other method) how I can make sure conversion takes place on the visuals?

Sorry for very basic questions and thank you very much for the help 🙂

parry2k
Super User
Super User

@KougaGennosuke this is good, but what is your currency table look like? Do you have an FX rate for each transaction date?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I should create currency table, but what I am struggling with is the format ie what to put in rows and columns. I know the applicable exchange rates on relevant dates.

parry2k
Super User
Super User

@KougaGennosuke can you share sample data and the expected output. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Sure.

 

This is a simplified data set, for 3 products for 3 years I have the following revenues/cost:

 

Product codeProduct nameREVENUES (EUR)Logistics cost (EUR)TAX paid (EUR)DATE
9874ABC3,345,439218,42363,21112/31/2018
1003XYZ6,2481,34912112/31/2018
1007OPQ71,4013,22435112/31/2018
9874ABC6,2529065512/31/2019
1003XYZ47,2521,72034812/31/2019
1007OPQ3,6871,31215312/31/2019
9874ABC552,57824,1682,84312/31/2020
1003XYZ853301812/31/2020
1007OPQ489,96415,1281,91512/31/2020

these are very basic charts i have created to show yearly data for each product separately in EUR (data curency):

 

KougaGennosuke_0-1642870390047.png

 

I would like to have another slicer where I can chose between EUR and USD and if I coose USD the very same graphs for one selected product is showing, just in USD.

 

I hope this clarifies the question 🙂

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!