Currency conversion

[KougaGennosuke]

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.

[v-henryk-mstf · Accepted 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.

[parry2k]

@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.

[KougaGennosuke]

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 🙂

[parry2k]

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

[KougaGennosuke]

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]

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

[KougaGennosuke]

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 🙂