Forum Discussion
Weekly billing amounts
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
Try with this measure:
Amt billed = Sumx(NATURALINNERJOIN(Table1,Table2), 'Billing Rate'[Billing Rate (USD)]*'Hours Worked'[Hours worked])
5 Replies
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])
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 Try with this measure:
Amt billed = Sumx(NATURALINNERJOIN(Table1,Table2), 'Billing Rate'[Billing Rate (USD)]*'Hours Worked'[Hours worked])
