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

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 !