Forum Discussion
Distribute amount proportional in a data flow transformation
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
| Region | product | costs |
| Europe | Product A | 60 |
| Europe | Product B | 120 |
| North America | Product C | 270 |
| North America | Product D | 90 |
Second table
| region | costs |
| Europe | 120 |
| North America | 400 |
expected result
| Region | Product | costs | distributed |
| Europe | Product A | 60 | 40 |
| Europe | Product B | 120 | 80 |
| North America | Product C | 270 | 300 |
| North America | Product D | 90 | 100 |
Any idea how to distribute Region costs based on Product costs per region?
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
