Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi,
I am trying to build a dashboard where the user would choose the currency for the sales from a slicer. Based on this selection, the base amount of the invoices expressed in SEK would get converted to their specified currency using the corresponding conversion rates (by year and currency).
If the end user would choose EUR from the slicer, in 2022 the amount should be 1500 SEK * 0.6744 = 1,011.6 EUR and so on. How can I achive it?
Desired outcome:
Year | Total Sales |
2019 | 809.28 |
2020 | 1686 |
2021 | 1449.96 |
2022 | 1011.6 |
DATA USED
Invoices
Invoice Number | Date | Amount (SEK) |
1 | 15-Sep-2021 | 100 |
2 | 25-Jan-2019 | 200 |
3 | 6-Jan-2019 | 500 |
4 | 22-Mar-2020 | 900 |
5 | 22-Apr-2022 | 300 |
6 | 15-Sep-2022 | 800 |
7 | 25-Jan-2020 | 1300 |
8 | 7-Jan-2019 | 500 |
9 | 20-Mar-2021 | 2000 |
10 | 10-Apr-2022 | 400 |
11 | 15-Sep-2020 | 300 |
12 | 20-Jan-2021 | 50 |
Currency Rates
exchangerate | year | currency_name | currency_code |
0.6535 | 2019 | Euro | EUR |
0.5781 | 2019 | Pound Sterling | GBP |
0.7718 | 2019 | US Dollar | USD |
0.5901 | 2020 | Pound Sterling | GBP |
0.6731 | 2020 | Euro | EUR |
0.7536 | 2020 | US Dollar | USD |
0.6538 | 2021 | Euro | EUR |
0.7455 | 2021 | US Dollar | USD |
0.5814 | 2021 | Pound Sterling | GBP |
0.6744 | 2022 | Euro | EUR |
0.5798 | 2022 | Pound Sterling | GBP |
0.7978 | 2022 | US Dollar | USD |
Thank you.
Solved! Go to Solution.
@Zosy , this can be done by creating these two measures:
Total Amount (SEK) = SUM('Invoices'[Amount (SEK)])
Total Sales (in selected currency) =
SUMX(
VALUES('Invoices'[Date].[Year]),
VAR vExchangeRate =
LOOKUPVALUE(
'CurrencyRates'[exchangerate],
CurrencyRates[currency_code], SELECTEDVALUE('CurrencyRates'[currency_code]),
CurrencyRates[year], 'Invoices'[Date].[YEAR]
)
RETURN
[Total Amount (SEK)] * vExchangeRate
)
This gives me these results:
Year | Total Sales (in selected currency) | currency_code |
2019 | 784.2 | EUR |
2019 | 693.72 | GBP |
2019 | 926.16 | USD |
2020 | 1682.75 | EUR |
2020 | 1475.25 | GBP |
2020 | 1884 | USD |
2021 | 1405.67 | EUR |
2021 | 1250.01 | GBP |
2021 | 1602.825 | USD |
2022 | 1011.6 | EUR |
2022 | 869.7 | GBP |
2022 | 1196.7 | USD |
@Zosy , this can be done by creating these two measures:
Total Amount (SEK) = SUM('Invoices'[Amount (SEK)])
Total Sales (in selected currency) =
SUMX(
VALUES('Invoices'[Date].[Year]),
VAR vExchangeRate =
LOOKUPVALUE(
'CurrencyRates'[exchangerate],
CurrencyRates[currency_code], SELECTEDVALUE('CurrencyRates'[currency_code]),
CurrencyRates[year], 'Invoices'[Date].[YEAR]
)
RETURN
[Total Amount (SEK)] * vExchangeRate
)
This gives me these results:
Year | Total Sales (in selected currency) | currency_code |
2019 | 784.2 | EUR |
2019 | 693.72 | GBP |
2019 | 926.16 | USD |
2020 | 1682.75 | EUR |
2020 | 1475.25 | GBP |
2020 | 1884 | USD |
2021 | 1405.67 | EUR |
2021 | 1250.01 | GBP |
2021 | 1602.825 | USD |
2022 | 1011.6 | EUR |
2022 | 869.7 | GBP |
2022 | 1196.7 | USD |
Thank you for your help! This worked easily with my data set.
Hi @Zosy
best and easiest method is to create a date table that contains the date and the year. Connect the date with the invoiced table and connect the year with currency exchange table. Then you measure would be
SUM ( Invoices[Amount (SEK)] ) * SELECTEVALUE ( 'Currency Rates'[exchangerate] )
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.