cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Calculated column between two tables

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

Try my new Power BI game Cross the River

View solution in original post

5 REPLIES 5
Highlighted
Resolver III
Resolver III

Re: Calculated column between two tables

Hi @barlevitzky

What about ....

Column4 = [Column2] - [Column3]

Hope That Helps

Highlighted
Microsoft
Microsoft

Re: Calculated column between two tables

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Calculated column between two tables

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

 

 

Highlighted
Super User III
Super User III

Re: Calculated column between two tables

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

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Helper II
Helper II

Re: Calculated column between two tables

Thanks ! 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors