Power Pivot Calculate issues

[BKNewton29]

I have a spread sheet that combines two excel data sources and four SQL data sources by bringing them into seperate sheets then combining them in MS Query onto a merged sheet.
I want to try and change this to come through Power Pivot

I believe I can't UNION ALL' excel and sql data sources into one table so I've now tried to create a calculated field that combines six seperate data connections in power pivot but this is where I'm stuck

I've tried =CALCULATE(sum(Test1[Actual Month 1]),(Test1[ReportCode])=1) but I actually want it to say
Test1[ReportCode] = 'an entry in the reportcode column in the data table that will amlgamate the figures'


I can't do a screen shot so below is what it looks like

Table for amalgamation

Report Type Report Code Calculated Field
Profit and Loss 7 xxxxx

Table Test1

Report Type Report Code Actual Month 1
Profit and Loss 7 2000
Profit and Loss 7 1000
Profit and Loss 1 10000

Any help appreciated

 

[BKNewton29]

Update

I've resolved this issue a different way

 

[RoryA]

Would you care to share in case it benefits someone else in future, please?