Forum Discussion
Cross reference joined tables
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
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.
2 Replies
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.
