Forum Discussion
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!
- Anonymous9 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
- AnonymousNot 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_powerbiFrequent 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!
- AnonymousNot 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