Aggregating a product of weekly hours charged and monthly charge rate up to a YTD value

[SignorSoprano]

I have a table of monthly sell rates and budgeted revenues for a group of business units, like below:

Business Unit	Month	Sell Rate	Budgeted Revenue
7	3/2020	130.132	735k
7	4/2020	129.131	800k
...	...	...	...
7	6/2021	131.243	1.2M

There are multiple business units listed in the table and for each business unit, there is an entry for every month in the fiscal year, which starts in July. For each month, there is an average sell rate and an expected revenue. The average sell rate is the average amount the company charges the client for each staff member working on the clients' projects.

I have another table which lists the daily timesheet transactions per staff member per project. It looks like this:

Project ID	Staff ID	Transaction Date	Period End Date	Hours
...	...	...	...	...

The period end dates are week-ending dates.

I have a staff table which links the Staff ID to the name of the staff and the business unit to which the staff belongs:

Staff ID	Staff Name	Business Unit	Supervisor ID	Supervisor Name	Email
..	..	..	...	..	...

And i have a date table with all dates starting from the start of the week corresponding to the earliest Period End Date in the timesheet table to the latest Period End Date in that same table.

I have a visual which sums up the total monthly hours per business unit and multiplies it by the average sell rate for that business group for that month (called the monthly effort rate) and then compares it to the monthly budgeted revenue for that business unit for that month. I have that part figured out.

What I now need is a visual which shows the YTD effort rate for each business unit. Being that the sell rates are per month, how can I first roll up the hours charged on a monthly basis, multiply it by the monthly sell rate to get the monthly effort rate and then sum up those monthly effort rates for the year to date calculation? Do I need to make an intermediate table?

I'm not sure if I'm missing something very obvious, but I can't figure this out for the life of me.

[v-easonf-msft]

Hi, @SignorSoprano 

I am not very clear about your current mode.Have you established a direct relationship between Transactions table and Staff table .

If possible, please  show relevant screenshots of the current model view.

Best Regards,
Community Support Team _ Eason

[SignorSoprano]

Sorry for the delay. I have been busy. But I took a screenshot of my data model and relationships. The Staff and Transactions tables are related by the Staff ID. The Staff and the Business Unit tables are related by the Staff ID. The Date and Transactions table are related by Date/Period End Date. My Budget and Business Unit tables are related by Business Unit ID. And my Budget and Date tables are related by Month/Month ID.

Here is the screenshot of my data model:

In that Fact Budgets table, as stated earlier, there is a Sell Rate field. I want to show the Effort Rate per month for each Business Unit. So I need to sum up the hours from the transactions table per business unit per month and then multiply that by the related Sell Rate for that month. I have accomplished this in a bar chart visual with the Month as the x axis, Business Unit in the legend and a measure which multiplies the hours * Sell Rate.

This was done before I asked my question. So, back to my question, I want to have another visual, let's say a bullet chart which has the target of the sum of the expected revenue for the current Fiscal Year and the value for comparison would be the current fiscal year-to-date effort rate value for each business unit.

Considering the format of my data, how can I sum up the hours for each business unit per month in order to multiply it by the sell rate of that month and then sum up those values to figure the current year-to-date value of the effort rate?

FYI, I have accomplished my goal by joining my transactions table to my staff table on the Staff ID to get the Business Unit ID in it and then joined that resulting table to the budgets table by Date and Business Unit ID in order to get the Sell Rate in the transactions table. This allowed me to easily sum up the values in any way I wanted.


But my original question is still something I would like to know. Within putting the sell rate into the transactions table, how would i get a YTD effort rate for each Business Unit?

[SignorSoprano]

Oh and my transactions table is called Fact BST in the screenshot. There are a lot of tables and relationships, I know. I have tried to reduce them as much as I can.

[MFelix]

Hi @SignorSoprano ,

Altough you have a complete description of your needs without data is difficult to help you, please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.