Forum Discussion
New column sum formula help
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
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.
6 Replies
Assuming a relationship between the tables:
Column = SUMX(RELATEDTABLE(IndividualCosts),[Cost])
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
Hi,
Try this
- 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
- From the first table, dag the ID to you visual
- Write the following measures
Total Funds = SUM(Table1[Funds])
Total Cost = SUM(Table2[Cost])
Hope this helps.
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
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.Tnx a lot.
I have had relationships, but i was writing bad punctations. I was typing , instead of ;
