Forum Discussion
Calculate the difference between the value in one table and the sum of values in another table
I have two tables. One table has many costs associated with one project and the other table has the revenue for the same project. I want to calculate the profit (Revenue-Cost=Profit).
Table 1 contains four columns: Employee, Project, Hours, Cost
Table 2 contains two columns: Project, Revenue
I am trying to create a New Column within Table 2 named Profit. Both tables are joined by Project name. Table 1 will have many entries (cost) for a single project name. Table 2 will have only one entry (revenue) for a single project name. Within the New Column in Table 2 I want to subtract the SUM of all Costs for a single Project in Table 1 from the Revenue for the matching Project in Table 2.
I'm just starting to work with Power BI and I'm not finding the DAX commands or format to help me.
Thank you.
My bad. I didn't properly understand your reply at first but after watching a video on DAX Functions I saw that I was not selecting "New Measure" to create the calculation. It works now. Thank you so much for your help.
3 Replies
SeanCommunity Champion
So create a Table Visualization
Place Project from Table 2 in this Table (I guess the 2 tables are related by the Project column and table2 has the unique values)
Create a Profit MEASURE = SUM ( Table2[Revenue] ) - SUM ( Table1[Cost] )
The way you describe the 2 tables that's all you need!
Good Luck! :smileyhappy:
Getting closer.
But I need to match the single Revenue value (by Project) within the Revenue table to the SUM of the Costs (by Project) in the Cost table.
So how do I identify the single project? In the equation you provided the value of the Profit MEASURE never changes, regardless of the Projects I select.
In the screen shot below, the answer to ARK-AUI should be $5,356. There is a single vaalue for revenue and multiple values for cost.
My bad. I didn't properly understand your reply at first but after watching a video on DAX Functions I saw that I was not selecting "New Measure" to create the calculation. It works now. Thank you so much for your help.