Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
111 | |
109 | |
94 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |