Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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 ;
Hi,
Try this
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |