Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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 |
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.
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.
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 |