How to sum revenue from all associated transactions

[ddoshi]

Thanks for your help in advance.

Data: 

Job#	LineItemType	Car Number	Crew Member	Revenue	Cost
1000	Labour		Tom	1000	500
1000	Labour		Richard	2000	1200
1000	Materials	A		2000	1000
1000	Materials	B		2500	1000
1000	Credit Memo			-500	0
1001	Labour		Harry	500	250
1001	Materials	C		1000	500

Desired output 1: 

	Total revenue for all crew in related jobs	Total revenue associated jobs
Tom	3000	7500
Richard	3000	7500
Harry	500	1500

Desired output 2:

	Total revenue for all cars in related jobs	Total revenue associated jobs
Car A	4500	7500
Car B	4500	7500
Car C	1000	1500

I am running into difficulties on how to calculate these sums because the fact-table doesn't have each crew or each car that is associated with each job on every row. Any thoughts on how to address this would be greatly appreciated!

[wdx223_Daniel · Accepted solution]

Total revenue associated jobs = IF(SELECTEDVALUE('Table'[Crew Member])<>"",CALCULATE(SUM('Table'[Revenue]),VALUES('Table'[Job#]),ALL('Table'[Crew Member]),'Table'[LineItemType]<>"Credit Memo"))

Total revenue associated jobs car = IF(SELECTEDVALUE('Table'[Car Number])<>"",CALCULATE(SUM('Table'[Revenue]),VALUES('Table'[Job#]),ALL('Table'[Car Number]),'Table'[LineItemType]<>"Credit Memo"))

Total revenue for all cars in related jobs = IF(SELECTEDVALUE('Table'[Car Number])<>"",CALCULATE(SUM('Table'[Revenue]),VALUES('Table'[Job#]),'Table'[Car Number]<>""))

Total revenue for all cars in related jobs = IF(SELECTEDVALUE('Table'[Car Number])<>"",CALCULATE(SUM('Table'[Revenue]),VALUES('Table'[Job#]),'Table'[Car Number]<>""))