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
I am trying to create a new calculated column in Table 1 in which based on the Invoice Currency and FY then the value is multiplied by the relevant currency rate within Table 2.
Table 3 shows the intended output i am trying to achieve.
Can you please advise on the dax measure required for this?
Solved! Go to Solution.
Hi @Anonymous
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Create a new calculated table, Table3:
Table3 =
ADDCOLUMNS (
Table1,
"FValue",
VAR rate_ =
LOOKUPVALUE (
Table2[Curreny Rate],
Table2[Financial Year], Table1[FY],
Table2[Currency], Table1[Invoice Currency]
)
RETURN
Table1[Value] * rate_
)
You could also just add a calculated column to your Table1 instead of creating a new table
This can also be done in Power Query.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Anonymous
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Create a new calculated table, Table3:
Table3 =
ADDCOLUMNS (
Table1,
"FValue",
VAR rate_ =
LOOKUPVALUE (
Table2[Curreny Rate],
Table2[Financial Year], Table1[FY],
Table2[Currency], Table1[Invoice Currency]
)
RETURN
Table1[Value] * rate_
)
You could also just add a calculated column to your Table1 instead of creating a new table
This can also be done in Power Query.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Many Thanks @AlB !!
Can you also advise on how to add calculated column in Table 1 instead and also how this can be done within Power Query?
@Anonymous
Calc colum for Table1:
FValue =
VAR rate_ =
LOOKUPVALUE (
Table2[Curreny Rate],
Table2[Financial Year], Table1[FY],
Table2[Currency], Table1[Invoice Currency]
)
RETURN
Table1[Value] * rate_
For the Power Query solution I'd need you to paste Table1 and Table2 here in text (just copy the table in PBI and paste here) so that I can copy the contents quickly and build the solution
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks for the swift response, see below.
PC Curr FY Value
abc | GBP | FY20 | 810.08 |
bcd | GBP | FY20 | 195.82 |
efg | GBP | FY21 | 380.69 |
hij | GBP | FY21 | 697.78 |
klm | EUR | FY20 | 956.07 |
mno | EUR | FY20 | 184.03 |
pqr | EUR | FY21 | 1785.86 |
stu | EUR | FY21 | 1572.71 |
Curr FYr Rate
GBP | FY20 | 1 |
EUR | FY20 | 1.129805 |
GBP | FY21 | 1 |
EUR | FY21 | 1.141068 |
@Anonymous
You need to create Table2 first. Then place the following M code in a blank query to see the steps. The last step is the important one (#"Added Custom")
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7LCgIxDAXQf8l6CEnHNulWULciuJDShTO+dXz7/9a6cKibXLgHLgkBlk0LFUyG03THC0MplAlJIVYBmnZVKnuLarKuN9u+copaCZ3PutsfSnVeUL7Lx1OXitF89lv21iFJ1u58KZV1gFRnvd7uff0ss2h6y2V+PF9/bMWgMMT4Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PC = _t, Curr = _t, FY = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PC", type text}, {"Curr", type text}, {"FY", type text}, {"Value", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "FValue", each [Value]*Table.SelectRows(Table2, (inner)=>inner[FY]=[FY] and inner[Curr]=[Curr])[Rate]{0}, type number)
in
#"Added Custom"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |