Forum Discussion
Calulating a remining budget
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
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.
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
5 Replies
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.
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.
Here's a calculated column that may work for you.
actualSpend = CALCULATE(Bookings[Bookings Amount], FILTER(Bookings,Bookings[Code] = Budgets[Code]))