Cross reference joined tables

[AndrewDang]

Hello all;

Thanks for looking at my post and question.  I have two fact tables and two reference tables that I had joined as the diagram below. 

Is there an easy way to create a Variance that the calculation is Variance = Forecast[Forecast Amount] - Actual [Actual Amount].  I had tried the RELATED() function but I can't seem to reference the Actual columns.

I am looking forward to your help on this.

Thanks;

Andrew

[chrisu · Accepted solution]

I may be making things too simple, but using the same filtering and table setup I was able to get the difference between the sum of [Forecast Amount] and the sum of [Actual Amount] with the following measure (you can change sum to average or max or whatever to suit your needs):

Variance = CALCULATE(SUM(Forecast[Forecast Amount]))-CALCULATE(SUM(Actual[Actual Amount]))

Obviously any slicers/filters/dimensions you use in your report visualizations would need to come from your Project Numbers and Date Dimension tables as your two fact tables don't have any filtering connections.  

[AndrewDang]

@chrisu

Thank you very much for your help. That works like a champs....

Have a great day.

Andrew