New column sum formula help

[gorankocinski]

hi,

I have one document with 2 tables in it.

In the first table there are 2 columns 

A with unic ID of some business and B with given funds by state.

for example 

Table name - GivenFunds

ID, Funds

12, 130

13, 200

14, 140

On the second table i have many columns for every single usage by every business but the most important are, 

A the ID of the business and B individual costs

for example 

Table name - IndividualCosts

ID, Cost

12, 3

12, 5

12, 1

12, 4

13, 6

13, 5

13, 5

14, 4

14, 4...

What i need is in the first table to create 3rd column where i will have sum of the costs of the individual costs by the business ID from the second table.

for example the first table should look like

ID, Funds, Costs

12, 130, 13

13, 200, 16

14, 140, 8

Thx for helping in advance

[v-xjiin-msft · Accepted solution]

Hi @Greg_Deckler,

In your scenario, have you created any relationship between the two tables? If not, please create one. Then you can try following calculated column:

Column =
CALCULATE (
    SUM ( SecondTable[Cost] ),
    FILTER ( FirstTable, FirstTable[ID] = EARLIER ( FirstTable[ID] ) )
)

And once you create the relationship @Greg_Deckler's solution should not return errors.

Thanks,
Xi Jin.

[gorankocinski]

Tnx a lot.

I have had relationships, but i was writing bad punctations. I was typing , instead of ;

[Ashish_Mathur]

Hi,

Try this

1. Assuming that there are no repetitions in the ID column of the first table, create a relationship from the ID column of the second table to the ID column of the first table
2. From the first table, dag the ID to you visual
3. Write the following measures

Total Funds = SUM(Table1[Funds])

Total Cost = SUM(Table2[Cost])

Hope this helps.

[gorankocinski]

@Ashish_MathurI dont need measures, i have done this in visual... now i need column with this data.

In Pivot in Excel this can be done in seconds, but i have problem with Power BI, I am new here

[Greg_Deckler]

Assuming a relationship between the tables:

Column = SUMX(RELATEDTABLE(IndividualCosts),[Cost])

[gorankocinski]

@Greg_DecklerI get error with this type of formula saying "Too many arguments were passed to the RELATEDTABLE function. The maximum argument count for the function is 1"

btw in the first table i have arround 100 unique ID's, and in the socond table i have arround 2 milion individual_cost rows