topic Sum column by distinct values of another column in DAX Commands and Tips

Sum column by distinct values of another column

JayReidy — Thu, 02 Mar 2023 06:48:49 GMT

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

Re: Sum column by distinct values of another column

tamerj1 — Thu, 02 Mar 2023 06:55:47 GMT

JayReidy

Please try

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

Re: Sum column by distinct values of another column

JayReidy — Thu, 02 Mar 2023 10:01:18 GMT

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

Re: Sum column by distinct values of another column

tamerj1 — Fri, 03 Mar 2023 05:15:54 GMT

JayReidy
Please try

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

Re: Sum column by distinct values of another column

FreemanZ — Sat, 04 Mar 2023 05:42:25 GMT

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?