March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to Solution.
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.
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.
@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
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:
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 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.
@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 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 🙂
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |