sum of earnings - single value for costs

[acnt_schartner]

Hi,

im new to PowerBI. I have a table.

Oder_ID	Project_ID	Project_Name	Earnings
1001	5001	Project A - Part1	100000
1002	5001	Project A - Part2	15000

And a Messure calculating project-costs for a Project ID (Costs for 5001 are 60000).

I have Earnings-Costs=CALCULATE(SUM(Table1[Earnings])-Table2[Costs]) and i get

Project_ID	Earnings	Costs	Earnings-Costs
5001	100000	60000	40000
5001	15000	60000	-45000

But i want:

Project_ID	Earnings	Costs	Earnings-Costs
5001	115000	60000	55000

Does anyone have a sollution?

Thanks in advance.

[Anonymous]

Earning measure=sum(Table[Earning])

This will return sum of values for id.

Cost measure=sum(Table[Cost])

It will return 120000 not 60000 as there are two same cost for same id.

Final measure=[Earning measure]-[Cost measure]

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[acnt_schartner · Accepted solution]

This was almost it and i figured it out.

The solution was:

Cost measure=sum(Table[Cost])

and a final messure

Final measure=[Earning measure]-[Costs]

Your line "Cost measure=sum(Table[Cost])" wasn't required. I could just use my Costs-Messure without "sum".

It seems to work in seperate Messures but not in an "CALCULATE()" like my original aproach

"Earnings-Costs=CALCULATE(SUM(Table1[Earnings])-Table2[Costs])"

Thank you. Your Reply helped me to aproach it differently. I'm still lerning.

[Anonymous]

if the cost remains constant then simple take max of cost.

Cost measure=Calculate(MAX(Table[Cost]))

Add project add, Earning measure and cost measure, and final measure in table visual.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[acnt_schartner]

CALCULATE(MAX(Table2[Costs]) doesnt work. Can't be used on a Messure.

Project_Costs  is constant for each Project_ID. Table1 is only an example. There are severel projects. Mostly with one order but some with 2 or 3.

[Anonymous]

Please share your exact dataset.

Tables with  column name so that it will be easy  for me to answer your quetion.

[acnt_schartner]

Thanks for your relpy.

The Messure for Project_Costs is a sum of everything spend for a Project_ID. I have 2xCost (in this case) if it is summed up .

The orders_table comes from a database where there can be multiple orders for a Project_ID.

I know its not great but that is the data i have.