Weekly billing amounts

[mikesdunbar]

Hello,

I'm trying to calculate the weekly billing amounts for individuals based on their job title. There are two spreadsheets that cover this.

First spreadsheet

Includes Job Code and Rate

Job Code	Billing Rate (USD)
Electrical	125
Mechanical	150
Project Manager	140
Systems	160
Director	190

Second spreadsheet

Has date (considered the start of a 7 day week), name, Job Code and hours worked

Date	Job Code	Name	Hours worked
9/10/2022	Electrical	Edward	15
9/17/2022	Mechanical	Mike	10
9/24/2022	Project Manager	Paul	20
10/1/2022	Systems	Stephanie	5
10/8/2022	Director	Donna	10
10/15/2022	Electrical	Edward	5
10/22/2022	Mechanical	Mike	20
10/29/2022	Project Manager	Paul	20
11/12/2022	Director	Donna	5

I'm trying to create a measure that would display in a matrix their hours worked and amount billed for that week.

Date	Name	Hours worked	Amount Billed
9/10/2022	Edward	15	$1,875
9/17/2022	Mike	10	$1,500
9/24/2022	Paul	20	$2,800
10/1/2022	Stephanie	5	$800
10/8/2022	Donna	10	$1,900
10/15/2022	Edward	5	$625
10/22/2022	Mike	20	$3,000
10/29/2022	Paul	20	$2,800
11/12/2022	Donna	5	$950

I'd like to keep the two spreadsheets separate since Billing Rates change, and the Hours Worked is exported from something else. 

Thanks!
Mike 

[CNENFRNL]

[Uspace87]

Hi,

Once you connect the two tables in your data model using the "Job Code" you can just write the following:

Amount Billed = Sum(First_Spreedsheet[Billing Rate (USD)]*Sum(Second_Spreedsheet[Hours worked])

[mikesdunbar]

So that's the equation that I started with, but it gives me a response way more than what it should be. Here's what it should be and what PowerBI calcultes.

Date	Job Code	Name	Hours worked	Billing Rate (USD)	What it should be	What Power BI calcs
9/10/2022	Electrical	Edward	15	125	1,875	11,475
9/17/2022	Mechanical	Mike	10	150	1,500	7,650
9/24/2022	Project Manager	Paul	20	140	2,800	15,300
10/1/2022	Systems	Stephanie	5	160	800	3,825
10/8/2022	Director	Donna	10	190	1,900	7,650
10/15/2022	Electrical	Edward	5	125	625	3,825
10/22/2022	Mechanical	Mike	20	150	3,000	15,300
10/29/2022	Project Manager	Paul	20	140	2,800	15,300
11/12/2022	Director	Donna	5	190	950	3,825

[Uspace87 · Accepted solution]

Try with this measure:

Amt billed = Sumx(NATURALINNERJOIN(Table1,Table2), 'Billing Rate'[Billing Rate (USD)]*'Hours Worked'[Hours worked])

[mikesdunbar]

That did it, thank you!