cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Profit Report

Hi there

I need to build a profit report as I have 3 tables as the following:

A- Table 1 contains:

1- Revenue amount

2- Revenue Date

3- Project ID

4 - Project Name

B- Table 2 Contains:

1- Project id

2- Cost amount

3- Cost Date

C- Table 3 Contains:

1- Project Id

2- Expense amount

3- Expense Date

my objective is to calculate the profit per project based on the time dimension of each table. The issue here is I cant build a relationship based on project ID and Time of each table.

Measures:

Actual Cost =  Cost amount + Expense amount

Profit = Actual Cost - Revenue amount

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: Profit Report

To create another new calculated table as a bridge table, and create relationship between your fact tables.

```Project =
DISTINCT (
UNION (
VALUES ( table1[projectid] ),
VALUES ( table2[projectid] ),
VALUES ( table3[projectid] )
)
)
```
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
4 REPLIES 4
Super User

## Re: Profit Report

I'd recommend to create a Project table as well. That will be connected by the Project ID and the Date table just by the Date columns.

Recommended reading: https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

Frequent Visitor

## Re: Profit Report

Thanks @ImkeF  for your response.

could you please clarify more

Super User

## Re: Profit Report

what exactly should be clarified?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

Highlighted
Community Support Team

## Re: Profit Report

To create another new calculated table as a bridge table, and create relationship between your fact tables.

```Project =
DISTINCT (
UNION (
VALUES ( table1[projectid] ),
VALUES ( table2[projectid] ),
VALUES ( table3[projectid] )
)
)
```
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

## Helpful resources

Announcements

#### New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,617)