3 Replies Latest reply on Oct 23, 2018 8:23 AM by Simon Runc

# Budget Reference Line multiplying by # of Resources

This might have been asked before if so please direct me to appropriate link. Thanks in advance for any help

I am showing a bar chart with week period (This represents only start date of the week) on the X axis and actual hours on Y axis. I am adding a Reference line to show budget hours per week both Budget and Actuals are associated to a cost pool #. I am getting Actuals broken down by resources assigned to each cost pool where as Budget is only at a Cost Pool and not broken down so when i am adding the reference line the budget is multiplying by # of resources assigned. Below is a scenario

Cost

Cost Pool #CatResourceBudget Hrs Week Date1
Budget Hrs Week Date 2
10001Thomas00
10002Thomas150150
10003Thomas350350
10001Eric00
10002Eric150150
10003Eric350350
20001Eric400400
20002Eric375375
20003Eric225225
Grand Total20002000
Expected Total to show on Reference Line15001500

So if both cost pools are selected in a filter i want to show the reference line total as 1500 instead of 2000 and when only one cost pool is selected say 1000 then i want to see reference line show 500 or if cost pool 2000 is selected then show Reference line at 1000.

• ###### 1. Re: Budget Reference Line multiplying by # of Resources

hi Tauseef,

A few ways you could do this. Simplest is probably to use an LoD

{FIXED [Pool#]: MIN([Budget])}

which will just take the MIN for each Pool...and this works over multiple pools correctly too.

Hope that does the trick

• ###### 2. Re: Budget Reference Line multiplying by # of Resources

Thanks for the quick reply Simon

I have updated the example when i use the LOD calculation Min comes out to 0 for the first cost pool. I would want the sum and then min of the sum per cost pool.

• ###### 3. Re: Budget Reference Line multiplying by # of Resources

Apologies, didn't spot the Cat Column!

So we can nest the LoD to take account of how we want it computed

{FIXED [Pool#]: SUM({FIXED [Pool#],[Cat]: MIN([Budget])})}

So this takes the MIN for each Pool#, Cat combination and then Sums them for each Pool#