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