Forum Discussion
divide by two different table columns
I have a calculated colmn called 'Driver Hours' [Payroll] and I want to divide that by a different table called 'LoadTrackingData'[TotalFreightRate]. When I click to add a new column to my current table and just try and divide i get an error. I need this to be on a per row basis
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
3 Replies
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.Those did not work for me. would it be because the table i am wanting to use is set a decmial number?
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
