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

[Nathan_powerbi]

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! 

[magsod · Accepted solution]

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]

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!

[magsod]

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]

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]

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?

Regards

[Nathan_powerbi]

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