Distribute amount proportional in a data flow transformation

Copper Contributor



I’m new to synapse and data flows, but try to solve this challenge. I’ve got two data streams. The first one with costs related to a product and the second on with costs related to a region. Now I want to distribute the costs of the region proportional based on the product price. I have no clue how to solve that because I need to aggregate 

EuropeProduct A60
EuropeProduct B120
North AmericaProduct C270
North AmericaProduct D90

Second table

North America400


expected result

EuropeProduct A6040
EuropeProduct B12080
North AmericaProduct C270300
North AmericaProduct D90100


Any idea how to distribute Region costs based on Product costs per region?

1 Reply


To solve this
1.set up a source for table 1. (source 1)
2.add an aggregation that groups by Region, and sums cost in column "RegionCosts"
3.add a an inner join between aggregation and source 1 on Region
4.add a source - source2 - for table 2
5.inner join derived column with source 2 on region
6.add derived column that multiplies cost from source1 with cost from source2 and divides this with RegionCost