Table Relationships Advice Needed

Hey Folks,

Im stumped with a problem:

I have a very granual Salesforce Object in a table which is my core data and is looking at pipeline values something along these lines:
There is about 1.3m rows in it:
Opportunity Table

Then I have a snapshot table which is quite aggregated:
Snapshot Table

On my dashboard I have summaries of the "core table":

Microsoft Current Pipeline Sales = 1m etc etc.. 

I now want to also show the related Pipeline Snapshot value

The way I tried to approach it is very very clumsy and I am not happy but I made a lookup key in both tables consisting of:
BU & Region & Close Qtr & Previous Week of Year (from another table calcing today values) & Opportunity Source

Then I did a lookupvalue formula to pull in the matching value into the Opportunity Table this obviously has repeating values so on my dashboard I did a measure for the MAX value... as clunky as it is it work WHEN the most granular filters are on but if someone does use those then it will obvioulsy bring back the MAX result from the table. I want the Sum.

So long story short
How best should I relate these 2 datasets together to present the Snapshot value allowing filters etc to work.

Thanks,

Fionn