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 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.
Solved! Go to Solution.
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.
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!
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.
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.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |