Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated Dax column

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?

 

Table 1Table 2

Table 3

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • AlB's avatar
    AlB
    5 years ago

    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 

     

     

     

     

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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 

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

      • AlB's avatar
        AlB
        Community Champion
        5 years ago

        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