Calculated column between two tables

[barlevitzky]

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      Column2               Column3

Date             Sum of Revenue    Sum of Cost

I want to create a calculated Column4 which will be profit (Revenue - Cost), these two are coming from different tables.

Thanks in advance.

[v-yuezhe-msft]

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

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 · Accepted solution]

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]

Thanks ! 

[vcastello]

Hi @barlevitzky

What about ....

Column4 = [Column2] - [Column3]

Hope That Helps