How to get the value once only from the blended data, when the left dataset makes it repeat in Google Data Studio?

I have blended 2 datasets, joining them by a couple of keys. The left dataset contains data for most of the dates, while the second one has monthly sales goals for each salesman. So, I'll have the daily sales, which when summed up, they give me the total, but when I sum the sales goals from the right dataset, it gets repeated for each sales person occurrence in the left one, giving me the wrong result. If I put it on a table visual and set its calculation to Average, it gives me the correct sales goal for each person, but the total is wrong and if I put it on a KPI visual, the total is also wrong.

Any help is appreciated.

Thank you!

Solution

Sorry, it is not possible to have the same field aggregated as average first and in the next step sum over these average values.

For the total of the "sales goal" can be extracted from right dataset.

If your data comes from Big Query, you could do following steps:

add an "empty" record for each month and sales person to the left dataset
join the two datasets in BigQuery
add a calculated column "goal per order", which is the "monthly sales goals" divided by the number of orders this salesmen had this month. This count number is count(orders) over (partition by salesmen, month_column)
In Data Studio the aggegated sum of the "goal per order" is the value for "goal per salesman"