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                           B        2

1                           C        3

2                           A        4

2                           B        5

2                           C        6

3                           A        7

3                           B        8  

3                           C        9 

 

Table 2

Projectnumber | Value score | ID

1                           3                  1

1                           2                  2

1                           5                  3

2                           1                  4

2                           0                  5

2                           0                  6

3                           2                  7

3                           1                  8

3                           4                  9

 

The IDs between the tables are connected (relationship). 

 

What we basically want is to multiply the value score using the following calculation:

"Calculation = A*(B+C)

We want to perform this calculation for every project individually. 

In the end we will have for every project one value instead of three.

 

To give an example for project number 1, the result would like: 3 * (2+5) = 21

These values should be saved into a new table that would like this:

 

 Projectnumber | Value score_multiplied

1                             21

2                             0

3                            10

 

 

Thank you very much in advance! An answer to this problem would really make our week! We would be so grateful! 

  • 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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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

    • Nathan_powerbi's avatar
      Nathan_powerbi
      Frequent Visitor

      Hi Magnus,

       

      Thank you very much for your quick answer. Really appreciate it!

       

      Nevertheless, I have question concerning the function VALUES. The maximum argument count for the function values is one and the following line contains two. What can we do to overcome this?

       

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

       

      Thanks in advance!

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable

        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