divide by two different table columns

jbenson , need some data

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Hi  jbenson ,

When you have the same column between two tables, you can use calculated column directly:

Table1：

Table2：

Create calculated column.

Column =
var _table2amount=SUMX(FILTER(ALL('Table2'),'Table1'[ID]='Table2'[ID]),[amount1])
return
DIVIDE(_table2amount,[amount])

When your two tables do not have related columns, you can add Index to both tables in Power query, so that there is a relationship between the two tables.

In Power query. Add Column – Index Column – From 1.

Table3：

Table4：

Create calculated column.

Column2 =
var _table4amount=SUMX(FILTER(ALL('Table4'),'Table3'[Index]='Table4'[Index]),'Table4'[amount1])
return
DIVIDE(_table4amount,[amount])

Please click here for the pbix file

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly