Forum Discussion
Calculated Dax column
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