Fixed Exchange Rate

[Dimitris_Kats]

Hello Everyone,

I have a small problem...i can think of a solution but I am not sure if its the best one so I will need your advice about that.

I have a table with sales, cost and profit per country and city for 2021.

I also have another table with fixed exchange rate. This rate won't change throughout the year it will stay stable.
I would like to have both options for my reports. Local Currency and euro.

The sales table is:

Year	Country	City	Sales (Local Currency)	Cost (Local Currency)	Profit (Local Currency)
2021	UK	London	100.000	33.333	66.667
2021	UK	Bristol	150.000	50.000	100.000
2021	UK	Liverpool	50.000	16.667	33.333
2021	France	Paris	300.000	100.000	200.000
2021	France	Marseille	60.000	20.000	40.000
2021	France	Lyon	45.000	15.000	30.000
2021	USA	NY	1.000.000	333.333	666.667
2021	USA	Arizona	600.000	200.000	400.000
2021	India	New Delhi	10.000.000	3.333.333	6.666.667
2021	India	Mumbai	8.500.000	2.833.333	5.666.667

The Exchange Rate table is:

Country	Exchange Rate
India	85,00000
UK	1,19000
France	1,00000
USA	1,11000

I was thinking to use a lookupvalue dax and add a calculated column in my sales table with the exchange rate column and create new columns for sales, cost and profit in euro. 

I was wondering if I can achieve the same results with measures and avoid creating so mane calculated columns.
Thank you very much in advance!!!

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I only created the sales amount, and I hope you can apply the same logic to Cost and Profit as well.

[Dimitris_Kats]

First of all thank you very much for your reply! I appreciate it!!

To be honest i wanted to have only 2 selections on the currency slicer : Local and euro.
If they select local currency they will see the sales exactly in the way I have them in the sales table. If they select euro they will have the sales in euro based on the exchange rates on the second table.
Do you think this is possible?

Thank you very much again for your time and help!

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create it in the next page in the attached pbix file.

please check the below picture and the attached pbix file.

Sales currency slicer: =
SWITCH (
    SELECTEDVALUE ( 'Currency Select'[Currency select] ),
    "Local", IF ( HASONEVALUE ( Sales[Country] ), SUM ( Sales[Sales (Local Currency)] ) ),
    "Euro",
        SUMX (
            sales,
            CALCULATE (
                DIVIDE (
                    SUM ( Sales[Sales (Local Currency)] ),
                    LOOKUPVALUE (
                        ExchangeRate[Exchange Rate],
                        ExchangeRate[Country], SELECTEDVALUE ( Sales[Country] )
                    )
                )
            )
        )
)

[Dimitris_Kats]

That's amazing and exactly what i need! 
Thank you so much!