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.
I'm working on a travel budget report. I have a 'Budget' table with everyone's last name in Column 1 and their alloted budget in Column 2, like this:
'Budget'
Name Budget
Anderson $1,000
In a second table I have all the 'Transactions' for the year. The transaction names contain (somewhere in the field) the last name of the related person, i.e.:
'Transactions'
Transaction Spend
Anderson cell phone $100
Hotel charge Anderson $200
I'm trying to create a Column 3 in the 'Budget' table that will sum Spend by Name. In other words, it will look at the Name column in the 'Budget' table, and then scan the Transaction column in the second 'Transactions' table for anything containing that Name, and sum the corresponding Spend. The above example would output this:
'Budget'
Name Budget Actual Spend by Name
Anderson $1,000 $300
I've tried several different ways but come up with an error every time. Any help is appreciated!
Solved! Go to Solution.
Hi @sfink22
Try this for your new column in the 'Budget' table:
Actual Spend by Name = CALCULATE ( SUM ( Transactions[Spend] ), FILTER ( Transactions, FIND ( Budget[Name], Transactions[Transaction], 1, 0 ) > 0 ) )
Hi @sfink22
SEARCH Function could be worked for you as well.
Column = SUMX ( FILTER ( Transactions, SEARCH ( Budget[Name], Transactions[Transaction], 1, 0 ) > 0 ), Transactions[Spend] )
Regards,
Cherie
Hi @sfink22
Try this for your new column in the 'Budget' table:
Actual Spend by Name = CALCULATE ( SUM ( Transactions[Spend] ), FILTER ( Transactions, FIND ( Budget[Name], Transactions[Transaction], 1, 0 ) > 0 ) )
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |