Sum of a column based on a value in a related table

[Anonymous]

Dear group, please help me with my predicament.

I have two related tables Projects 

Project	Budget	ContractedHours
A	47280.00	283.00
B	14240.00	89.00
C	319940.00	5017.37
D	352416.00	4635.12
E	102216.00	653.85

And Timesheets

Employee	Project	Chargeable
1	A	0
2	B	30
3	C	35
4	D	0
5	A	0
1	B	185
2	C	159.25
3	D	0
4	A	0
5	B	23
1	C	0
2	D	67.5
3	A	0
4	B	39.5
5	C	18.5

And I want to do couple of things. 
First a measure which calculates all chargeable hours.

ChargeableHours = SUM(Timesheets[Chargeable])

Because the tables are related (by projectid) I can use it in a table visual (for projects) and it will correctly show all chargeable hours for each project.

Project	Biudget	Contracted	ChargeableHours
A	47280.00	283.00	0
B	14240.00	89.00	277.5
C	319940.00	5017.37	212.75
D	352416.00	4635.12	67.5
E	102216.00	653.85

What I am looking for is an average contracted rate for all those projects which have registered chargeable hours in the timesheet table. 
I struggle with the next step, which is a measure which will sum all the Budget values from Projects, but only, where ChargeableHours were more than 0 and a measure which will do the same with contracted hours under the same circumstances. I could then achieve what I want by dividing the measure 1 by 2. 
Any thoughts?

[Jihwan_Kim]

Hi, @Anonymous 

I am not sure how your logic for the last result looks like, but please check the below picture and the sample pbix file's link down below.

Budget Total =

SUMX (

Timesheets,

Timesheets[Chargeable] * RELATED ( Projects[Budget] )

/ RELATED ( Projects[ContractedHours] )

)

https://www.dropbox.com/s/lfh2yvt8bdgqhzx/chmurzynski.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi Jihwan,

Thanks for your reply,

I am looking for 3 measures.

1. A total of budgets for those projects which have registered chargeable hours in the timesheet table. SUM of Projects Budget WHERE ChargeableHours >0
2. A total of Contracted hours for those projects which have registered chargeable hours in the timesheet table. SUM of Projects Contracted WHERE ChargeableHours >0
3. An average contracted rate for those projects which have registered chargeable hours in the timesheet table. 1 divided by 2

EDIT: Those are three independent measures and I don't intend to add them to any of the columns of the table visual.