Help with association

[#1]

I need to break down sales quotas by month. My quotas xls file has quota broken down by month as "qtMonth":

My master Calendar table has a "Month" field. In order to show the quota by month, I need to associate the qtMonth to Month, but I don't want an actual assocition in my model as my quotas table (FY14NewBusiness below) is associated by TerritoryID. You can see in the chart below that quotas are being summed for all months, and not individual. What's the best way to solve this?

 

[Gysbert_Wassenaar]

Perhaps you need to consolidate that table with another fact table. See this blog post: Fact Table with Mixed Granularity

[#3]

Hi,

Try with a Bridge Table:

Tmp_BridgeTable:

Load distinct [Month] Resident Calendar;

Join

Load [TerritoryID], [qtMonth] Resident FY14NewBusiness ;

Bridge_Business_Calendar:

Load distinct [(Month], [TerritoryID]

Resident Tmp_BridgeTable

Where [Month] = [qtMonth] ;

Drop Table Tmp_BridgeTable ;

Regards.

[#4]

Although this solution seemed like a good idea, it still caused a loop associated by Month to the Calendar table. It may make more sense to see the entire model:

[#5]

Thanks for the reply Gysbert although I'm having trouble applying it to my situation. Can you expand on how generic keys could help me link my tables by month and territory please?