Union of 2 tables based on several columns

[Anonymous]

Hi everyone,

I looking for help to union 2 tables, it starts to get over my head. I would like to present on a same graphic the CAPEX spend vs the budget each month.

I have this first table with my budget per month:

Projetct name	Business / IT CAPEX	Date	Amount ($)
Project #1	Business CAPEX	JANUARY 23	$450 000
Project #1	IT CAPEX	JANUARY 23	$450 000
Project #2	Business CAPEX	JANUARY 23	$450 000
Project #2	IT CAPEX	FEBRUARY 23	$450 000

I have a second table with what has actually been spend each month on each project :

Project name	Business / IT CAPEX	Request by	Date	Amount
Project #1	Business CAPEX	A	JANUARY 2023	$150 000
Project #1	Business CAPEX	B	JANUARY 2023	$50 000
Project #1	IT CAPEX	C	JANUARY 2023	$70 000
Project #1	Business CAPEX	A	FEBRUARY 2023	$40 000
Project #2	IT CAPEX	R	JANUARY 2023	$40 000
Project #2	IT CAPEX	A	FEBRUARY 2023	$20 000

I would like to be able to consolidate the data in one table in order to be able to display a chart showing whether the project is under or over the budget month after month. 

Would you have any idea how this can be done in PowerBI?

Many thanks for the support this community may offer.

Kindest regards

[Anonymous]

Thanks a lot CalebR, it worked perfectly!

[CalebR]

No problem glad I could help!

[CalebR · Accepted solution]

I would relate these two tables together. 
Create a new "Full name"column in Table1 & Table 2 that concats the "Project Name" & "Business/ IT name" together. That will allow you a 1 to many relationship to join them on. Then its as simple as throwing the Full Name column into a visual, drag the original budget field next to it. Then create a SumofSpent measure that takes the sum of amount from table2. Should end up with something like this: 

FullName	Budget	Spent
Project#1 Business Capex	450000	200000