How to add new column to table based on sum of values based on unique ID

[ddalton]

Hi,

I have the following tables:

Table 1. 

ID	REF	ValueA
252	555	1000
261	560	1500
300	562	1350
324	578	2000
...	...	...
...	...	...

Table 2.

Index	ID	ValueB
1	252	100
2	252	150
3	261	500
4	300	100
5	324	150
6	324	150
...	...	...
...	...	...

The following relationship exists between Table1 and Table2:

Table1	Relationship	Table2
ID	1:*	ID

I want to create a column in Table1 that, based on unique ID, is effectively:

Column = Sum(Table2[ValueB]) + Table1[ValueA])

Such that Table 1 would look like:

Table 1. 

ID	REF	ValueA	NewValue
252	555	1000	1250
261	560	1500	2000
300	562	1350	1450
324	578	2000	2300
...	...	...
...	...	...

So far, my solution has been to create a new table:

Table = SUMMARIZE(Table2,Table2[ID],"ValueC",SUM(Table2[ValueB]),"ValueA",SUMMARIZE(Table1,Table1[ValueA]))

And then create a new column in that table:

Column = ('Table'[ValueC]+'Table'[ValueA])

Which gives Table3:

ID	ValueC	ValueA	Column
252	250	1000	1250
261	500	1500	2000
300	100	1350	1450
324	300	2000	2300
...		...
...		...

Then, I can just add the new Column to a table or matrix visualisation. 

However, instead of creating a new table first, I would prefer to simply create a new column in Table1 to the same effect in a single expression but I just can't figure out the DAX. 

[SpartaBI]

@ddalton Table 1 and table 2 has a 1 to many relationship bases on the ID column?

[ddalton]

@SpartaBI Yes, I should've mentioned that. 

I've added the relationship to the original post but for clarity it's here too:

Table1	Relationship	Table2
ID	1:*	ID

[SpartaBI]

@ddalton ok, so I already replied a sec after my other post 🙂

[SpartaBI · Accepted solution]

@ddalton if yes, like this:

Then this is your calc column:

Column = 'Table 1'[ValueA] + SUMX(RELATEDTABLE('Table 2'), 'Table 2'[ValueB])

Showcase Report – Contoso By SpartaBI

      

[ddalton]

That's brilliant! Thank you. 

I had tried using SUMX but I wasn't entirely sure how it worked and wasn't aware of the RELATEDTABLE function. 

[SpartaBI]

@ddalton my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[ddalton]

Thanks. I'll check it out!