Newbie question: The simplest idea seems to be the most difficult.

[qwaszx55]

I am trying to do some simple math and can't for the life of me figure out the correct formula to use.

I have one table that includes project number and revenue

I have another table that has multiple entries of project number and costs

I Just want a table that shows revenue, cost, and then profit (revenue - cost)

I'm using 

"Cost = SUMX(VALUES(JournalDetailView[PROJECTNO]),CALCULATE(SUM(JournalDetailView[Amounts])))"

To total the costs into a new measure called "COST". The revenue however is in a different table. 

So this will be a dumb question but how do I do Projects[Revenue} - JournalDetailView[COST]

Should I be shaping the data before hand to put it all together? 

[Greg_Deckler]

Sample data and structure would be helpful, but the answer likely lies in creating measures for revenue and cost and then another measure that subtracts your cost measure from you revenue measure.

[qwaszx55]

Table One (Projects)

ProjectNO | Revenue

1                |       $10

2                |       $15

3                |       $20

Table Two(JournalDetailView)

ProjectNO | Amounts

1                | $2

1                | $5

2                | $10

2                | $1

2                | $1

3                | $5 

Then doing:

"Cost = SUMX(VALUES(JournalDetailView[PROJECTNO]),CALCULATE(SUM(JournalDetailView[Amounts])))"

To total the costs for each project. The revenue however is in a different table. 

So:

"then another measure that subtracts your cost measure from you revenue measure."

Is where I'm stuck. Seems easy enough but the fact that they are in different tables seems to be stopping me.

[Greg_Deckler · Accepted solution]

Given the data you describe and an assumed 1-to-many relationship between Projects and DetailedJournalView create the following measures:

RevenueTotal = SUM(Projects[Revenue])

TotalAmount = SUM(JournalDetailView[Amounts])

Difference = [RevenueTotal] - [TotalAmount]

Create a visualization (table) with ProjectNO from the Projects table and these three measures. I get:

ProjectNO	RevenueTotal	TotalAmount	Difference
1	10	7	3
2	15	12	3
3	20	5	15