Forum Discussion
How to divide data from two colums coming from different data sources
Hello,
I am new to power bi and exploring its capabilities. I have built a table from two different data sources (August Payroll and August Production). I have created a relationship between these two data sources and the data is displaying properly. My issue is this: I would like to add a new column to my table (Boxes per Hour). How can I add a column that divides my values from my Boxes colum by Hours column to get boxes per hour?
I have tried using New Column as follows: Boxes per Hour = DIVIDE('August Production'[Boxes],
This is as far as I get. Because my Hours column is coming from a different data source (August Payroll) it wont give me the option to choose it. It only gives me the option to choose from the August Production data file as seen in pic 2. Any suggestions would GREATlY be appreciated.
Hi,
Refer the below file with boxes per hour column.
https://www.dropbox.com/s/0qg1x6n67vmmj3x/August%20Harvest.pbix?dl=0
I think you should soend some time to learn DAX, so some of the usefull youtuube channels where you can learn it.
https://www.youtube.com/channel/UCy2rBgj4M1tzK-urTZ28zcA
10 Replies
Hi
You need to use RELATEDTABLE function to call columns from other related table.
https://msdn.microsoft.com/en-us/library/ee634226.aspx
= SUMX( RELATEDTABLE('InternetSales_USD') , [SalesAmount_USD])Thank you for your reply. I tried the related table formula and i could not get it to work. After some research, I tried to use the New Measure feature instead of New Colum. Using this feature, I was able to reference both tables in the formula but I still get an error message. I first tried it without the SUM function and got an error. The training video i was watching recommended using the sum function but it unfortunately is not working for me.
Any other recommendations?
Hi
Without looking at your data model it's difficult to give you a proper solution, (if possible share with me some dummy data of your two tables) however I tried to imitate your scenario and my PBIX file is attached.
kindly check my steps and check whether you’re getting the result you want.
https://www.dropbox.com/s/mtmtgzd0xcfluq4/maximus1.pbix?dl=0
