cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kristinak Frequent Visitor
Frequent Visitor

Calculated column from two different tables

Hello,

 

I am new to Power BI and DAX and I am facing some difficilties. I want to add a calculated column where I need to devide values from two different tables. Relationship between tables is Many to one (I suppose).

The syntax I write is the following:

 

ColumnName = DIVIDE('Table1[Stock], RELATEDTABLE('Table2)[RQTY])

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: Calculated column from two different tables

@kristinak,

 

Firstly, you need to get data from antoher table, and then devide values. To get data from another table, we cna use RELATED function and Lookupvalue function.
RELATED function returns a related value from another table.
https://msdn.microsoft.com/en-us/library/ee634202.aspx

Lookupvalue function returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value.
https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

If this is not what you want, please provide us some sample data and your expected result, so that we can make further analysis.

 

Regards,

Charlie Liao

 

4 REPLIES 4
kristinak Frequent Visitor
Frequent Visitor

Calculated column with values from two tables

Hello,

 

I am new to Power BI and DAX and I am facing some difficilties. I want to add a calculated column where I need to devide values from two different tables. Relationship between tables is Many to one (I suppose).

The syntax I write is the following:

 

ColumnName = DIVIDE('Table1[Stock], RELATEDTABLE('Table2)[RQTY])

 

Thanks in advance.

Super User
Super User

Re: Calculated column with values from two tables

@kristinak

1. what issue are you facing exactly i.e what errors are you getting?

2. can you provide a screenshot of your relationship view?

3.  do you need a calculated column or measure?

 

 

 

 

 

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Moderator v-caliao-msft
Moderator

Re: Calculated column from two different tables

@kristinak,

 

Firstly, you need to get data from antoher table, and then devide values. To get data from another table, we cna use RELATED function and Lookupvalue function.
RELATED function returns a related value from another table.
https://msdn.microsoft.com/en-us/library/ee634202.aspx

Lookupvalue function returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value.
https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

If this is not what you want, please provide us some sample data and your expected result, so that we can make further analysis.

 

Regards,

Charlie Liao

 

kristinak Frequent Visitor
Frequent Visitor

Re: Calculated column from two different tables

Hi again. I figured out I need to use the relatedtable function. 
Let me bring some clarity regarding my intentions. I have two tables, 1 with the monthly inventory report and one (dashboard) with PO data. The relationship that I have created between those two tables is One to manywith crossfilter direction: both. 

Now, what I am trying to calculate in a new calculated column is the sum of inventory (from the monthly report) / sum of PO Qty

 

 

My understanding is, when I need to calculate the Months of stock, I need to divide the current level of inventory qty / qty consumed in the last month. 

 

MOS = DIVIDE(RELATED('Inventory Level'[Stock]), 'Dashboard'[GRQTY]), 0)

 

I am pretty sure I do something wrong either with the relationship between the two tables, or with the DAX syntaxis/functions.  

Thank you for your help.