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 all, I am rather stuck on the following problem and wonder if anyone can advise?
I have a table loaded with values in a single column which are a mixture of currencies.
Period | Line | Value | Currency |
201912 | PAY ICO-- | -5,437,330.00 | EUR |
202000 | PAY A ICO-- | -5,400,297.41 | EUR |
201812 | PAY ICO-- | -4,763,415.20 | EUR |
201900 | PAY A ICO-- | -4,595,511.31 | EUR |
201900 | PAY ICO-- | -4,562,300.84 | CAD |
201800 | LIA OTH-- | -4,506,109.05 | EUR |
201812 | INC OTH-INT- | -4,414,344.44 | EUR |
201800 | PAY A ICO-- | -4,225,874.50 | EUR |
201912 | WIP-EXT- | -4,060,620.32 | GBP |
202007 | REC A ICO-- | -4,041,224.00 | EUR |
201912 | INC OTH-INT- | -4,024,194.23 | EUR |
I also have a table of conversion rates
Period | EUR | CAD | GBP | CZK | US |
202000 | 1 | 1.455604076 | 0.846202677 | 25.42423757 | 1.122700752 |
202001 | 1 | 1.465075181 | 0.839898964 | 25.21925042 | 1.108084542 |
202002 | 1 | 1.478878153 | 0.860161867 | 25.45347221 | 1.102707385 |
202003 | 1 | 1.556267183 | 0.885086397 | 27.35171173 | 1.102358727 |
202004 | 1 | 1.5206582 | 0.867365751 | 27.08086703 | 1.093243896 |
202005 | 1 | 1.529053548 | 0.899557905 | 26.92582537 | 1.110623282 |
202006 | 1 | 1.529841269 | 0.908327248 | 26.6434015 | 1.123464042 |
202007 | 1 | 1.581933778 | 0.900287439 | 26.2527609 | 1.18192734 |
202008 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
202009 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
202010 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
202011 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
202012 | 1 | 1.557661801 | 0.893449343 | 26.26782629 | 1.195324388 |
I've also unpivoted the table to create this (not sure which is best option)
Period | Currency | Rate |
202000 | EUR | 1 |
202000 | CAD | 1.455604 |
202000 | GBP | 0.846203 |
202000 | CZK | 25.42424 |
202000 | USD | 1.122701 |
202001 | EUR | 1 |
202001 | CAD | 1.465075 |
202001 | GBP | 0.839899 |
202001 | CZK | 25.21925 |
202001 | USD | 1.108085 |
202002 | EUR | 1 |
202002 | CAD | 1.478878 |
202002 | GBP | 0.860162 |
202002 | CZK | 25.45347 |
202002 | USD | 1.102707 |
202003 | EUR | 1 |
202003 | CAD | 1.556267 |
202003 | GBP | 0.885086 |
202003 | CZK | 27.35171 |
202003 | USD | 1.102359 |
202004 | EUR | 1 |
202004 | CAD | 1.520658 |
202004 | GBP | 0.867366 |
202004 | CZK | 27.08087 |
202004 | USD | 1.093244 |
202005 | EUR | 1 |
202005 | CAD | 1.529054 |
202005 | GBP | 0.899558 |
202005 | CZK | 26.92583 |
202005 | USD | 1.110623 |
202006 | EUR | 1 |
202006 | CAD | 1.529841 |
I have created a quick measure to calculate the average rates.
I would like to create an additional column in the first table which lists the value of each line in EUR as per below:
Period | Line | Value | Currency | CalculatedValue in € |
201912 | PAY ICO-- | -5,437,330.00 | EUR | -5,437,330.00 |
202000 | PAY A ICO-- | -5,400,297.41 | EUR | -5,400,297.41 |
201812 | PAY ICO-- | -4,763,415.20 | EUR | -4,763,415.20 |
201900 | PAY A ICO-- | -4,595,511.31 | EUR | -4,595,511.31 |
201900 | PAY ICO-- | -4,562,300.84 | CAD | -251,326.98 |
201800 | LIA OTH-- | -4,506,109.05 | EUR | -4,506,109.05 |
201812 | INC OTH-INT- | -4,414,344.44 | EUR | -4,414,344.44 |
201800 | PAY A ICO-- | -4,225,874.50 | EUR | -4,225,874.50 |
201912 | WIP-EXT- | -4,060,620.32 | GBP | -4,449,345.55 |
202007 | REC A ICO-- | -4,041,224.00 | EUR | -4,041,224.00 |
201912 | INC OTH-INT- | -4,024,194.23 | EUR | -4,024,194.23 |
I know I need to lookup the currecny column and apply the conversion, however, I am struggling to write the expression.
Can anyone please help with this?
Many thanks in advance!
Alex
Solved! Go to Solution.
@artfulmunkeey Hi You can achieve the result without doing unpivoting the columns. What I understand that have two tables like below
You can create a calculated column in your table 2 like
Let me know if you are expecting some other output.
Proud to be a Super User!
@artfulmunkeey Hi You can achieve the result without doing unpivoting the columns. What I understand that have two tables like below
You can create a calculated column in your table 2 like
Let me know if you are expecting some other output.
Proud to be a Super User!
Perfect! Thanks so much, that's exactly what I needed
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |