cancel
Showing results for
Did you mean:
Highlighted
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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: Calculated column between two tables

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
5 REPLIES 5
Highlighted
Resolver III

## Re: Calculated column between two tables

Hi @barlevitzky

Column4 = [Column2] - [Column3]

Hope That Helps

Highlighted
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

## Re: Calculated column between two tables

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

## Re: Calculated column between two tables

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
Highlighted
Helper II

## Re: Calculated column between two tables

Thanks !

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

#### 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!

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

Top Solution Authors
Top Kudoed Authors