Sum column by distinct values of another column

[JayReidy]

Hey

I have a table, simplified has these values

name	Email	$$$
Campaign1	test1@email.com	1000
Campaign2	test2@email.com	2500
Campaign2	test3@email.com	500
Campaign1	test3@email.com	5000
Campaign2	test1@email.com	850

I want to create a measure to sum all the $$ amounts that are a reference to another tables column. In a table that would look like this

name	$$
Campaign1	$6000
Campaign2	$3850

I was trying to use =SUMX(DISTINCT(campaign[email]),campaign[othertable.$$])) 

But it won't let me use the other tables column in the measure, I've made this column using advanced editor and extracting that information from relationship fields.

What am I doing wrong here?

Sorry for the terrible formatting of tables it's the first one I've done and I kept getting HTML errors

[FreemanZ]

hi @JayReidy 

as you have already get the campaign[othertable.$$] column with Power Query Editor, you can just try:

measure = SUMX(campaign),campaign[othertable.$$]))

Or?

[tamerj1]

@JayReidy 

Please try

=SUMX(DISTINCT(campaign[email]),RELATED(campaign[othertable.$$])))

[JayReidy]

No doesn't work sorry forgot to mention this is an indirect relationship

[tamerj1]

@JayReidy 
Please try

=
SUMX (
    DISTINCT ( campaign[email] ),
    SUMX (
        FILTER ( othertable, othertable[email] = campaign[email] ),
        othertable[Value.$$]
    )
)