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.
Hi,
Hope im posting this to the right channel!
I have two excel based dataset
Dataset 1: Quarterly USD exchange rate table
Quarter | Local CCY | USD xrate |
4-2020 | SEK | 9.700 |
4-2020 | NOK | 9.120 |
4-2020 | DKK | 6.100 |
4-2020 | EUR | 0.799 |
4-2020 | USD | 1.000 |
3-2020 | SEK | 9.200 |
3-2020 | NOK | 9.620 |
3-2020 | DKK | 6.100 |
3-2020 | EUR | 0.911 |
3-2020 | USD | 1.000 |
Dataset 2: Sales data in a table
Quarter | Local CCY | Total sales (local CCY) |
4-2020 | SEK | 5,114,999 |
4-2020 | NOK | 4,105,999 |
4-2020 | DKK | 2,116,999 |
4-2020 | EUR | 111,999 |
4-2020 | USD | 113,999 |
3-2020 | SEK | 4,113,999 |
3-2020 | NOK | 2,13,999 |
3-2020 | DKK | 1,113,999 |
3-2020 | EUR | 113,999 |
3-2020 | USD | 113,999 |
End result I would want to produce in Table visualisation in POWER BI:
Quarter | Local CCY | Total sales (local CCY) | Total sales (USD) |
4-2020 | SEK | 5,114,999 | |
4-2020 | NOK | 4,105,999 | |
4-2020 | DKK | 2,116,999 | |
4-2020 | EUR | 111,999 | |
4-2020 | USD | 113,999 | |
3-2020 | SEK | 4,113,999 | |
3-2020 | NOK | 2,13,999 | |
3-2020 | DKK | 1,113,999 | |
3-2020 | EUR | 110,999 | |
3-2020 | USD | 116,999 |
Thank you very much for your help in advance.
Solved! Go to Solution.
@Anonymous - Well, you could either create a calculated column or measure like this:
Calculated Column = DIVIDE('Dataset 2'[Total sales (local CCY)],LOOKUPVALUE('Dataset 1'[USD xrate],'Dataset 1'[Quarter],'Dataset 2'[Quarter],'Dataset 1'[Local CCY],'Dataset 2'[Local CCY]),0)
Or you could create a concatenated column in both tables, [Quarter] & "-" & [Local CCY] and form a relationship between them and then you could just use RELATED or RELATEDTABLE in your calculation instead of LOOKUPVALUE.
Hi @Anonymous
If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!
If not, please kindly elaborate more.
@Anonymous - Well, you could either create a calculated column or measure like this:
Calculated Column = DIVIDE('Dataset 2'[Total sales (local CCY)],LOOKUPVALUE('Dataset 1'[USD xrate],'Dataset 1'[Quarter],'Dataset 2'[Quarter],'Dataset 1'[Local CCY],'Dataset 2'[Local CCY]),0)
Or you could create a concatenated column in both tables, [Quarter] & "-" & [Local CCY] and form a relationship between them and then you could just use RELATED or RELATEDTABLE in your calculation instead of LOOKUPVALUE.
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |