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