Forum Discussion
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
Hi Anonymous ,
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] ) ) )
4 Replies
Hi Anonymous
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/
Hi Anonymous ,
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] ) ) )
