Distribute amount proportional in a data flow transformation

Copper Contributor

Hey,

 

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 

Regionproductcosts
EuropeProduct A60
EuropeProduct B120
North AmericaProduct C270
North AmericaProduct D90


Second table

regioncosts
Europe120
North America400

 

expected result

RegionProductcostsdistributed
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

@gerritlo 

Hi
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

 

skongstad_0-1670849271513.png