Forum Discussion

GCGradwell's avatar
GCGradwell
Helper I
10 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

 

Column X is in table 1

Column Y is in table 2

 

When creating Z in table 1, it does not see columns from any other tables. There is a relationship between both tables which I have checked is still there.

 

This is very strange, I have created calculated columns using column data from 2 separate tables many times before and never had any problems.

 

Any ideas?

 

 

Thanks,

 

Greg

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 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] ) )

14 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 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] ) )
    • IanSamTaylor's avatar
      IanSamTaylor
      New Member
      8 years ago

      Hi,

      I am new to Power BI .  Can I use data in tables to calculate more data?

       

      My example is: I have Forecasted Product sales for 2 weeks. Prod 1, 100 units, Prod 2, 200 units.

       

      I have a table with the Sales Mix % by Week, Week 1, 75%, week 2, 25%

       

      Can I calculate out these to two table to create a new table with forecasted sales by week.

      eg

      Week1, Prod1, 75 units

      Week2, Prod1, 25 units

      Week1, Prod2, 150 units

      Week2, Prod2, 50 units

       

      I am calculating this in excel and then loading to power BI. 

       

      Thanks

      Sam

    • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      What if it's a Many to many relationship? 

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    You are asking very general questions, so it is hard to know what you are trying to do, or why.  My experience is that most calculated columns that new users try to build are completetly un-necessary and probably worse still - it normally is the wrong approach.  Read my article here, then see if you can do what you want with measures.

     

    http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

  • CahabaData's avatar
    CahabaData
    Memorable Member
    10 years ago

    as someone coming to Power BI from databases - my immediate question of your post is - where is the unique ID (key) field for these two tables?  In order to any math one must know which row's value to use on both sides......

     

    Perhaps that's a given - just thought I would throw that in....   in which case your expression would be involving Related Table....