Calulating a remining budget

[MarkCalvert]

Hello, 

I'm wanting to calucate a remaining budget figure to show on the report. 

Data

Budgets - each code has a budget on this sheet

Timesheet bookings - all the bookings (with  actual costs)  are made to this sheet and can be identified with a code

In the report I have a total budget and a total spend, I'm wanting to show the remaining budget (Budget - acutals)

My thoughts are that I need a new measure in the budgets data sheet which sums up all the actual costs based on the code allocated, unless there is a quicker and better way to do this?

Budgets Sheet

Code - Budget

IF01 - £1,250

IF02 - £900

IF03 - £600

Bookings Sheet

Name- Code - Amount

Bob - IF01 - £300

Steve - IF02 - £200

Steve - IF01 - £50

Henry - IF03 - £300

Henry - IF02 - £150

Bob - IF02 - £200 

[ChrisMendoza · Accepted solution]

hello @MarkCalvert,

I've been struggling with a similar type issue. In saying that, I am not exactly sure how you're going to present your figures in your report. My presumption is that when you subtract Actuals from Budget you end up with something like:

Where the Budget per Code is repeated and the math just doesn't make sense in the nested row context. 

From my understanding this is a grainularity issue, meaning the budget is only allocated at Code not by Name.

You can modify the formula's to acheive a visual that only shows the values when they make sense (the grainularity they were meant for) or you can re-allocate the budget based on the name dynamically (which is where I'm finding my struggle):

Budget Amount =
IF(
    ISFILTERED(Bookings[Name]),
    BLANK(),
    SUM(Budgets[Budget])
) 

Budget - Actual = 
IF(
    ISFILTERED(Bookings[Name]),
    BLANK(),
    [Budget Amount] - Bookings[Bookings Amount]
)

Budgeting Technique that I watched https://www.sqlbi.com/tv/budgeting-with-power-bi-pass-austria/ which you may find helpful.

[ChrisMendoza · Accepted solution]

hello @MarkCalvert,

I've been struggling with a similar type issue. In saying that, I am not exactly sure how you're going to present your figures in your report. My presumption is that when you subtract Actuals from Budget you end up with something like:

Where the Budget per Code is repeated and the math just doesn't make sense in the nested row context. 

From my understanding this is a grainularity issue, meaning the budget is only allocated at Code not by Name.

You can modify the formula's to acheive a visual that only shows the values when they make sense (the grainularity they were meant for) or you can re-allocate the budget based on the name dynamically (which is where I'm finding my struggle):

Budget Amount =
IF(
    ISFILTERED(Bookings[Name]),
    BLANK(),
    SUM(Budgets[Budget])
) 

Budget - Actual = 
IF(
    ISFILTERED(Bookings[Name]),
    BLANK(),
    [Budget Amount] - Bookings[Bookings Amount]
)

Budgeting Technique that I watched https://www.sqlbi.com/tv/budgeting-with-power-bi-pass-austria/ which you may find helpful.

[MarkCalvert]

I've attached what one data table looks like, and I'd like to add the actuals from the bookings, it's easy to do in the report view and I suspect it's easy but I'm being a bit thick here. 

[ChrisMendoza]

@MarkCalvert,

Here's a calculated column that may work for you.

actualSpend = CALCULATE(Bookings[Bookings Amount], FILTER(Bookings,Bookings[Code] = Budgets[Code]))

[MarkCalvert · Accepted solution]

The coding there doesnt seem to work, but I did try

Spend per code = CALCULATE(SUM('PMD Data'[Amount]))

(PMD Data[amount] being the name and it worked a treat. 

Thank you most kindly for the help, pointed me in the right direction

[ChrisMendoza]

Ah, yes, in my code 

Bookings Amount = SUM(Bookings[Amount])

as a measure. I used the measure as the argument.

actualSpend =
CALCULATE(
  Bookings[Bookings Amount],
  FILTER(Bookings,Bookings[Code] = Budgets[Code])
)