Applying daily fee cap then proportionally dividing this cap across different entries

[Anonymous]

Hi,

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!

[amitchandak · Accepted solution]

@Anonymous , I am assuming there is date and client in the table in you table

You can have column or measure like

sub = calculate(sum(Table[Hour]), allexcept(Table, Table[Date], Table[Client]))

If columns then new column

if([sub] > 9, [Hour]/[Sub] *2000, [Hour]/[Sub] *250)

If you create a measure, then a new measure

sumx(summarize(Table, [Date], [clinet], [project], "_1", if([sub] > 9, Sum([Hour])/[Sub] *2000, sum([Hour])/[Sub] *250) ),[_1])

[Anonymous]

Awesome.

Made a slight tweak to the calculated 'Sub' column to sum by name of Associate and it's done what's needed. 

Thank you!