Forum Discussion
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.
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] ) ) )
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.
7 Replies
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 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.
Yes, this is tremendous help, thank you.
I created a sample FX table:
And made a connection between dates:
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 🙂
KougaGennosuke can you share sample data and the expected output.
Sure.
This is a simplified data set, for 3 products for 3 years I have the following revenues/cost:
Product code Product name REVENUES (EUR) Logistics cost (EUR) TAX paid (EUR) DATE 9874 ABC 3,345,439 218,423 63,211 12/31/2018 1003 XYZ 6,248 1,349 121 12/31/2018 1007 OPQ 71,401 3,224 351 12/31/2018 9874 ABC 6,252 906 55 12/31/2019 1003 XYZ 47,252 1,720 348 12/31/2019 1007 OPQ 3,687 1,312 153 12/31/2019 9874 ABC 552,578 24,168 2,843 12/31/2020 1003 XYZ 853 301 8 12/31/2020 1007 OPQ 489,964 15,128 1,915 12/31/2020 these are very basic charts i have created to show yearly data for each product separately in EUR (data curency):
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 🙂
KougaGennosuke this is good, but what is your currency table look like? Do you have an FX rate for each transaction date?
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.
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] ) ) )
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.
