Applying daily fee cap then proportionally dividing this cap across different entries
Where I work, we charge out our time to client by hours.
For certain clients, we have 'day rate' fee caps, where is we work beyond a certain number of hours daily, the day rate is charged instead.
A simple example is below:
Associate hourly rate = $250
Associate day rate (triggered when >8 hrs worked) = $2000
So if the Associate works more than 8hrs, we will never charge beyond $2000
However, sometimes that same Associate is working across multiple projects for this client, so that day rate needs to be proportionally divided across those projects, example below:
Associate has worked a total of 9.5hrs across three projects on the same day as such:
1. Project A = 3.5hrs
2. Project B = 3.0hrs
3. Project C = 3.0hrs
As the total aggregate hours = 9.5hrs, the day rate of $2000 is triggered and applied. Currently I am calculating this manually in Excel for this particular client and then dividing the $2000 day rate proportionally by the hours worked so it calculates as below:
1. Project A = 3.5hrs ($2000/9.5 x 3.5 = $736.84)
2. Project B = 3.0hrs ($2000/9.5 x 3.0 = £631.58)
3. Project C = 3.0hrs ($2000/9.5 x 3.0 = £631.58)
I am keen to see if it's possible to get this working in PowerBI. Currently I can only get it working for when they are working on one project daily only using a lookup table for "Hourly vs Day Rates" and applying the day rates when total daily hours >8, but am stumped as to how I could get it working when there are multiple projects being worked on that same day like in the example above?
Have to admit, this is something that has been puzzling me for a few months now, so any ideas would be welcome!