Forum Discussion

GCGradwell's avatar
GCGradwell
Helper I
9 years ago
Solved

Create a Calculated Column from Different Tables

Hi ,   I can’t work out how to create a calculated column using 2 columns from different tables in the formula.   I’m trying to create a calculated column ‘Z’ using a simple formula: X – Y   Co...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    GCGradwell

     

    Based on my understanding, it depends on the relationship.

     

    If 1:1, below formula should work.

    calculated column =
table1[X] - RELATED ( table2[Y] )

    If 1:many, the following formula should work.

    calculated column =
oneTable[X]
    - CALCULATE ( SUM ( manyTable[Y] ), ALLEXCEPT ( manyTable, Table10[linkedCol] ) )
AlwaysStuck's avatar
AlwaysStuck
New Member
8 years ago

I'm trying to do a similar thing but can't get the formula you provided to work.

 

I have two tables and want to subtract Column A from Table 1 from Column B from Table 2.  There is a relationship between the two tables via a common ID. 

 

 

The relationship is one to many whereby the value being subtracted is the 'one table' and the one from which data is being subtracted from, is the 'many table'.

 

The data is in date format.

 

i.e 05/03/2017 - 03/03/2017 = 2

 

Any help appreciated.

 

Thanks

Vanalakshmi's avatar
Vanalakshmi
Frequent Visitor
8 years ago

Hi ,

 

i am trying to create a calculated column using 2 different tables. i am getting error.

 

My query:

 

=if(table1[column1] = "abc"  || table1[column1]  =  "bcd", "Others",  if( table1[column1]  ="xyz" ,  if( table2[column2]  = "pqr", "Oman", if( table2[column2] = "que", "hyz", "ecc"))))

 

Error:

 

column 'column2' in 'table2' cannot be found or may not be used in this expression.

 

please suggest proper syntax.

 

thank you.