Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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
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
Those did not work for me. would it be because the table i am wanting to use is set a decmial number?
@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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |