How to summarize daily sales data with quarterly sales targets?

[christopher_doe]

I have daily sales data (from Salesforce, if that’s helpful) that I’d like to summarize on quarterly, regional dimensions. 

Consider the following sales table -- we’ll call this explore “Accounts and Opportunities”

I also have the following quarterly, regional sales targets table - we’ll call this explore “Sales Targets”

I’d like to join these data sets so that I can create the following tables as looks for my end users: 

When I join these two explores (on region & year-quarter), I get the following when I try to summarize: 

We can see the target amount is way off from above -- in fact it’s a multiple of the number of sales records we have because the join did the following: 

I’m now incorrectly summing the quarterly target as if it’s a daily amount like the sales data. 

What’s the best way to combine these two data sets so that I can get to my ideal summary results as shown above?

Keep in mind that there are a number of other dimensions from the “Accounts and Opportunities” table that my users may want to filter on. There are also many more regions. I want to avoid doing this all through table calculations as it’s incredibly manual and inflexible. I want to be able to maintain my “Sales Targets” table outside of Looker. 

Thanks!

[christopher_doe]

The answer is changing the measure in my view: 

Previous:

  measure: target_amount {
    type: sum
    sql: ${TABLE}.target_amount ;;
  }

Updated: changed sum to sum_distinct

  measure: target_amount {
    type: sum_distinct
    sql: ${TABLE}.target_amount ;;
  }