Forum Discussion
summarizing multiple values from different Table within other Table
Hi,
I do have two tables. one is simply a long list of values structured like below
| search value | cost a | cost b | final cost |
| 1z32sde | 12 | 32 | 44 |
| 1z8432 | 12 | 23 | 11 |
| in general text-number mix | in general a formular calculating different values based on other criteria |
second table is
| Info | final cost | ||
| 1z32sde | 44 | ||
| 1z8432 | 11 | ||
Within the second table i do have an "Info" who may exist twice or multiple times on this table and I do need to search it on the other table in column "Final cost". Those "Final cost" might be already another calculated value but is always a number.
the Info I do need to search may exist twice, sometimes just once, and sometimes even 10 times, and sometimes does not exist. All Values needs to be summarized and given as feedback on the second table where I do have to use it afterward to do some more calculation. the search value is also something I can not write in the formula and it will be taken of a different column.
in Excel it is so easy and iam able to use endless SUMIFS formula referencing to other columns without pain. but thats not how power bi works...
any recommendation?
Hi Anonymous ,
You can try this code to create a calculated column in second table.
Column = CALCULATE ( SUM ( 'First Table'[Final cost] ), FILTER ( 'First Table', CONTAINSSTRING ( 'First Table'[Search value], 'Second Table'[Info] ) ) )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous , Not very clear a new column in table 1
Maxx(filter(Table2, Table1[Search value] = Table2[Info]) , Table2[final Cost])
If this does not help
Can you a sample output in table format?refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8Hi amitchandak,
apologize for not beeing clear enough.
i do build the table in Word, now it also contains the border to make it more clear
the second table contains the "Info" i do need to search in the first table in the column "search value" and it shoud Summarize all the "final cost" from the first table and it should be on the second table in column "Final cost - summary" available.
First table
Search value
Cost A
Cost B
Final cost
1Z32sde
12
32
44
1z8432
12
23
11
1zwert32456
12
27
15
1z098761
43
2221
554
1z32sde
22
123
456
In General text-number mix
123
In general a formular calculating different values based on other criteria
Second Table
Info
Final cost - summary
1Z32sde
500
1zwert32456
15
1z098761
554
1Z32sde
500
so for the value "1Z32sde" it would deliver 50 as feedback as in the first table are "2" values available summarized together as 500.
i hope that helps.
p.s. thanks for the max formular. I do wish power bi would work on mac as well. Then i would be able to play and increase my knowledge in my free time, too.
Hi Anonymous ,
You can try this code to create a calculated column in second table.
Column = CALCULATE ( SUM ( 'First Table'[Final cost] ), FILTER ( 'First Table', CONTAINSSTRING ( 'First Table'[Search value], 'Second Table'[Info] ) ) )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
