Calculated column with values from an unrelated table

[Drue]

Hey folks! I am running into an issue that I can't solve, and can't find an answer to in the forums.

First and foremost, here is my file.

I have a table Budget and a table GL that are not related to eachother, but are both related to the table Account and the table Calendar. I would like to add a new calculated column to my Budget table under the data section that says:

If the Budget account=36 then find the sum of GL actuals within the same month where account=192, multiply that total by 0.03 and add the budget amount to it, otherwise just put the budget amount.

For account code 36 (Subcontractors), our budget is actually the flat amount in the file plus 3% of that month's revenue. My budget file is pulling directly from QuickBooks, and QB can't do that kind of math, so I want to do it in PBI, since that is where I am reporting from. 

My goal is to create a table that looks like this with the correct Budget amount (right now not including 3% of revenue), so there can be a side-by-side comparison of actual vs budget

Because I want it to look like this, I don't think I can create a measure. Or at least, I haven't figured out how to make that all work. I am open to how to do this, as long as I can get the above visual.

Thanks in advance for any help you can give!!

[Drue · Accepted solution]

I was actually able to finally solve my own problem, adding a calculated column to the data. Here is the forumla that worked:

New Budget =

IF(

Budget[Account]="36",

(CALCULATE(SUM(GL[Actual]),

FILTER(ALL(GL), MONTH(Budget[Date])=MONTH(GL[Date]) && YEAR(Budget[Date])=YEAR(GL[Date]) && GL[Actual]>0))*0.03)+Budget[Budget],

Budget[Budget]

)

[lbendlin]

"I would like to add a new calculated column to my Budget table"

That won't work as your budget table doesn't have the complete date information - unless you can positively exclude scenarios where you have actuals for an account+date but no budget (in which case you could merge the Budget and GL tables). Your sample data looks rather different

I think you would rather need a measure.