How to divide data from two colums coming from different data sources

[maximus1]

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.

[dilumd]

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]) 

[maximus1]

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?

[dilumd]

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

[maximus1]

dilumd,

Thank you for your help. I tried to replicate your formula but I still get an error message.

Under my August Production, I tried to replicate your formula as a new measure and as a new column and get an error on both. 

I have attached my data below. Thank you for taking the time, i really appreciate all the help.

https://www.dropbox.com/s/m9vv6zbxjjtebu9/August%20Harvest.pbix?dl=0

[v-ljerr-msft]

Hi @maximus1,

In addition to @dilumd's solution, you should also be able to simply use the formula below to create a measure to calculate Boxes per Hour in this scenario. 

Measure = DIVIDE(SUM('August Production'[Boxes]),SUM('August Payroll'[Hours]))

Regards

[maximus1]

v-ljerr-msft

Thank you for your help! I appreciate the alternative solution. If you know of any resources that would be helpful in learnig DAX please let me know. I am new to power bi, but really want to learn.

Thank you again!

[v-ljerr-msft]

Hi @maximus1,

Based on my experience, it's better to start learning DAX from a book, as there are some key concepts needs to be understand first(i.e. the difference between row context and filter context). 

Regards

[maximus1]

Thank you!

[dilumd · Accepted solution]

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

https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw

[maximus1]

Thank you, dilumd!

I will definitely spent some time learning DAX. I appreciate all your help. If there is any books on DAX that you would recommend please let me know.