Forum Discussion

barlevitzky's avatar
barlevitzky
Helper I
8 years ago
Solved

Calculated column between two tables

Hi, I have 2 tables: 1. Revenue - each row is a sale. 2. Costs - Each row is a cost action. The only Connection between these two tables is the date, so I made a table like this: Column1      Colu...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi barlevitzky

     

    You ought to use "Calculate". Calculate transforms ROW CONTEXT into FILTER CONTEXT

     

     

    In Column2 use (Modify the table name according to your situation)

    =calculate(Sum(RevenueTable[Revenue])

    In Column3 use (Modify the table name according to your situation)

    =calculate(Sum(CostTable[Cost])

Anonymous's avatar
Anonymous
Not applicable
8 years ago

barlevitzky,

Do you have 1:1 relationship between the two tables? If so, create column4 using the DAX below. If not, please share sample data of the two tables and tell us which table have duplicate dates in Date column.

Column4 = Revenue[Column2] - RELATED(Costs[Column3])


Regards,
Lydia

  • barlevitzky's avatar
    barlevitzky
    Helper I
    8 years ago

    Hi, Thanks for your reply.

    The connection between the tables is many to many and made by a third Table. 

    When I am typing "Related.." PowerBI suggests me only columns that exist is this third table. (I need a column from the first table).

     

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi barlevitzky

       

      You ought to use "Calculate". Calculate transforms ROW CONTEXT into FILTER CONTEXT

       

       

      In Column2 use (Modify the table name according to your situation)

      =calculate(Sum(RevenueTable[Revenue])

      In Column3 use (Modify the table name according to your situation)

      =calculate(Sum(CostTable[Cost])

      • barlevitzky's avatar
        barlevitzky
        Helper I
        8 years ago

        Thanks !