divide by two different table columns

[jbenson]

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 

[amitchandak · Accepted solution]

@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.

[Anonymous · Accepted solution]

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

[jbenson]

Those did not work for me. would it be because the table i am wanting to use is set a decmial number? 

[amitchandak · Accepted solution]

@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.