Forum Discussion

Nathan_powerbi's avatar
Nathan_powerbi
Frequent Visitor
9 years ago
Solved

Create new table based on calculations between columns from two different columns

Dear Power BI users,   We are struggling to do a calculation using the following tables:   Table 1 Projectnumber | Type | ID 1                           A        1 1                           ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi,

     

    If you first create a new column in Table2  named Type= Related(Table1[Type]).

     

    Then you can create a new calculated table with the following dax code:

    NewTable = SUMMARIZE (
        Table2;
        Table2[ProjNum];
        "Value_Score_Multiplied"; CALCULATE ( VALUES ( Table2[Value] ); FILTER ( Table2; Table2[Type] = "A" ) )
            * (
                CALCULATE ( VALUES ( Table2[Value] ); FILTER ( 'Table2'; Table2[Type] = "B" ) )
                    + CALCULATE ( VALUES ( 'Table2'[Value] ); FILTER ( Table2; Table2[Type] = "C" ) )
            )
    )

     

    Br,

    Magnus

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi,

 

If you look again at the code you will see that VALUES is the argument for the CALCULATE function. The only argument for the VALUES is: Table2[Value]

 

CALCULATE ( VALUES ( Table2[Value] ); FILTER ( Table2; Table2[Type] = "A" ) )

 

That part will fetch the only availble value in the column [Value] where the column [Type] is equal to A.

 

The full line of code that I wrote in my first reply is verified and works to get the calculated table you asked for.

/Magnus

Nathan_powerbi's avatar
Nathan_powerbi
Frequent Visitor
9 years ago

Thank you Magnus, we missed a bracket. You are our hero! We managed! We would have never succeeded on our own. Thank you so much! I do not know how but if I could ever help you.. ;)

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Nathan_powerbi,

     

    Great to hear the problem got resolved! Could you accept the helpful reply as solution to help others who may have similar issue easily find the answer and close this thread?:smileyhappy:

     

    Regards

    • Nathan_powerbi's avatar
      Nathan_powerbi
      Frequent Visitor
      9 years ago

      Thanks again! I just did! You are the man!