Forum Discussion
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 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.
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])
5 Replies
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,
LydiaHi, 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).
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])
Hi barlevitzky
What about ....
Column4 = [Column2] - [Column3]
Hope That Helps
