Summing Table 1 based on criteria in Table 2

[TRADER083]

Hi everyone,

I have a very simple question but couldn't make it work. I have Table 1 and 2 which are 1-to-1 relationship. I want to create a SUM column in Table 2 which sums the value from Table 1 but filter by Table 2's criteria column. There are multiple criteria, showing one for simplicity.

Table 1

Key	Value
1	100
2	200
3	400

Table 2

Key	Criteria	Sum
1	A	300
2	A	300
3	B	400

Thank you,

Kevin

[FreemanZ · Accepted solution]

try to create a column with the code below:

SUM =

VAR CurrentCriteria = Table2[Criteria]

RETURN

CALCULATE(

    SUM(Table1[Value]),

    ALL(),

    Table2[Criteria] = CurrentCriteria

)

I tried and it worked like this:

[FreemanZ]

or with this code:

SUM2 =

CALCULATE(

    SUM(Table1[Value]),

    ALLEXCEPT(Table2,Table2[Criteria])

)