Lookup value based on multiple criteria from another table

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

Need Help

VahidDM

4 years ago

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: