Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.