cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alan_c
New Member

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

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!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@alan_c , 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])

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@alan_c , 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])

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!