This is a tough one in Looker Studio. When you blend together two tables on a join condition that has multiple shared values, the data will fanout as you're seeing here.
(In Looker, there are many ways to deal with fanout. You can define a join as many_to_many, for example! And you can create fields like sum_distinct that let you automatically remove fanout on a field. But Looker Studio doesn't have these options, so we'll have to get hacky.)
There is a workaround, though it's not very clean. You can compute the fanout number and then divide your metrics by that.
For example, you said that you wanted the qtySold to be 391. When I blended these two tables together, I got 1564 for qtySold. Notice that 1564/391 = 8. Why is this a round number, and where is the 8 coming from? Well, the qtySold is being repeated once for each of the 4 fctSegment values and once for each of the 2 dates in the tblForecast table.
So, we can "de-duplicate" the value by creating a new metric that divides out the duplicate values we create through the blend. The formula for a corrected qtySold total would look like this:
SUM(qtySold) / (
COUNT_DISTINCT(fctSegment)
* COUNT_DISTINCT(date_tblForecast)
)
You would have to do this for each metric that is overinflated. That's the best I've got, but it still feels pretty hacky. Does anyone else have ideas on avoiding fanout when blending in Looker Studio?
LinkedIn
Twitter
