Having some issues in blending the two tables to get a proper pivot chat table in looker studio.

Looker Q&A Looker Studio
1

i have 2 tables.
1 is tblForecast and another is tblSales.
tblForecast details is as follows:

date itemCode fctSegment fctQty fctRevenue
2025/01/01 20008 Restaurant 5100 15339604
2025/01/01 20008 Retail 330 1175764
2025/01/01 20008 Web 4 14000
2025/01/01 20008 Wholesale 8086 25126718
2025/02/01 20008 Restaurant 5100 15339604
2025/02/01 20008 Retail 330 1175764
2025/02/01 20008 Web 4 14000
2025/02/01 20008 Wholesale 8086 25126718

for each item everymonth forecast qty and forecast revenue is set for four segments (Retail,web,wholesale and restuarant)
here are the few records

Now i have another table as sales data. this is transactional table:

Date itemCode qtySold revenue salesTeam customerSegment costCenter ActualSegment
2025/01/01 20008 1 3150 Kinki 2.Wholesale M1 Wholesale
2025/01/04 20008 1 3675 4.Retail Shop_SOK Retail
2025/01/04 20008 1 3675 SHOP KRM 4.Retail Shop_KRM Retail
2025/01/04 20008 5 14750 SHOP KRM 1.Restaurant Shop_KRM Retail
2025/01/04 20008 3 11025 4.Retail Shop_SML Retail
2025/01/04 20008 2 7350 4.Retail Shop_NSK Retail
2025/01/05 20008 1 3675 4.Retail Shop_SML Retail
2025/01/05 20008 1 3675 4.Retail Shop_NSK Retail
2025/01/05 20008 2 5900 SHOP KRM 1.Restaurant Shop_KRM Retail
2025/01/05 20008 2 7350 SHOP KRM 4.Retail Shop_SML Retail
2025/01/05 20008 4 12600 SHOP SOK 2.Wholesale Shop_SOK Retail
2025/01/05 20008 2 7350 4.Retail Shop_SOK Retail
2025/01/06 20008 1 3675 4.Retail Shop_SML Retail
2025/01/06 20008 1 3675 4.Retail Shop_NSK Retail
2025/01/06 20008 1 2950 Hokkaido 1.Restaurant M1 Restaurant
2025/01/06 20008 1 2950 Kanto 1.Restaurant M1 Restaurant
2025/01/06 20008 20 66000 KBS 2.Wholesale M2 Wholesale
2025/01/06 20008 8 23600 SHOP SOK 1.Restaurant Shop_SOK Retail
2025/01/06 20008 22 77000 4.Retail Ambika_Web Web
2025/01/06 20008 2 7350 4.Retail Shop_SOK Retail
2025/01/06 20008 2 5900 5.Staff (Restaurant) M1 Retail
2025/01/06 20008 28 88200 Wholesale 1 2.Wholesale M1 Wholesale
2025/01/06 20008 32 100800 Wholesale 2 2.Wholesale M1 Wholesale
2025/01/06 20008 5 14750 Group 1.Restaurant M1 Restaurant
2025/01/06 20008 7 20650 Kanagawa 1.Restaurant M1 Restaurant
2025/01/06 20008 8 23600 Kobe 1.Restaurant M1 Restaurant
2025/01/06 20008 7 20650 Chubu 1.Restaurant M1 Restaurant
2025/01/06 20008 48 143148 Japanese 1.Restaurant M1 Restaurant
2025/01/06 20008 28 77600 Tokyo 1.Restaurant M1 Restaurant
2025/01/07 20008 1 3675 4.Retail Shop_KRM Retail
2025/01/07 20008 1 2950 SHOP KRM 1.Restaurant Shop_KRM Retail
2025/01/07 20008 4 13200 KBS 2.Wholesale M2 Wholesale
2025/01/07 20008 4 11800 SHOP SOK 1.Restaurant Shop_SOK Retail
2025/01/07 20008 46 144900 SHOP SOK 2.Wholesale Shop_SOK Retail
2025/01/07 20008 22 77000 4.Retail Ambika_Web Web
2025/01/07 20008 5 14750 Tokyo 3.Other M1 Retail
2025/01/07 20008 48 143200 Group 2.Wholesale M1 Wholesale
2025/01/07 20008 14 44100 Chubu 2.Wholesale M1 Wholesale

now i want to create a blend where i can get a chart table (piovt) in the following way
when i select the date control from 1st Jan to 07st jan
itemCode, fctQty, qtySold, sold%,fctRevenue, Revenue
20008, 13520, 391, 2.89%, 41656086, 1222248..
when i change the date to Feb, it changes accordingly..
I hope you unestrand the problem i getting because fctTble has single date (for each mont) but salesTable is a transactional table so it has same date and many other date in the same month multiple times. Pleas help! i have tried using chatgpt..but fail!.

2

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?