summarizing multiple values from different Table within other Table

[Anonymous]

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?

[Anonymous]

apologize, my fault within the words... "1Z32sde" would deliver 500 as "Final cost-summary"

[Anonymous · Accepted solution]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@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=czNHt7UXIe8

[Anonymous]

Hi 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.