Lookup value based on multiple criteria from another table

[Anonymous]

Hi, how do I look up and sum up value based on on multiple criterias from another table?

Here, the criterias are Date and Name.

My first table is 

Date	Name	Total
Dec	A	100
Dec	A	200
Dec	B	100
June	A	0
June	C	0

My second table 

Date	Name	Amount
Dec	A	5
Dec	A	10
Dec	B	1
June	A	5
June	C	23
June	C	5

Required output:

Date	Name	Total	Amount
Dec	A	300	15
Dec	B	100	1
June	A	0	5
June	C	0	28

Thank you.

[V-pazhen-msft]

@Anonymous 

The easiest method is groupby and add index to the 2 tables, link the 2 tables with index. 

Then you can just drag columns into a table visual.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[VahidDM · Accepted solution]

Hi @Anonymous 

Add one column as a Date-Name to both the First and the Second Table with this code:

Date-Name = 'Second Table'[Date]&"-"&'Second Table'[Name]

Then try this code to add a new table:

Table =
ADDCOLUMNS (
    SUMMARIZE (
        'First Table',
        'First Table'[Date],
        'First Table'[Name],
        "Total", SUM ( 'First Table'[Total] )
    ),
    "Amount",
        CALCULATE (
            SUM ( 'Second Table'[Amount] ),
            ALLEXCEPT ( 'Second Table', 'Second Table'[Date-Name] )
        )
)

Output:

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

Appreciate your Kudos✌️!!