Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Solved! Go to 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] )
)
)
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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!
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] )
)
)
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
That's amazing and exactly what i need!
Thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |