Hi all - I noticed some already closed threads that discussed this type of need, but none that fit into my scenario. Can anyone suggest the best expression(s) to accomplish this?
* I am calculating a Billable FTE to NonBillable Ratio with the following formula: (sum([Billable Hrs]) / 40) / (count(distinct {<[BillableFlag]-= {'0'}>} Resource)) --> For easy math, lets assume this results in 20 / 10 = 2.0 as the billable to nonbillable ratio [B2NB] for 1 given period.
* I then want to factor in the avg Billing rate by doing: sum([Billable Hrs] * [Billing Rate]) / sum([Billable Hrs]) --> For easy math for one week time period, lets assume this results in $180 avg billing rate.
* I can plot these as two expressions but its not telling a good story.
My Dimension is Week, and I want to able to show the adjusted billing rate against the Billable to NonBillable ratio [B2NB], leveraging the deltas between all Week dimensions.
So for example:
on 1/1/2014, the B2NB was 2.0 and the Avg. Bill Rate was $140
on 1/1/2015, the B2NB was 2.0 and the Avg. Bill Rate was $180
note: my chart would show 52 dates (1 per week) per year, I am just explaining 1 specific delta.
The requirement provided to me is: (but I am hoping the community can either help with the correct syntax or offer a better idea).
Expression1:
Avg. Bill Rate Current week - "That" week's Avg Bill Rate = Delta (note: negative's can/should be possible)
100% - (Delta / Current Week Bill Rate)
Expression2:
Value from Expression1 * B2NB Ratio
Thanks so much for any specific suggestions
