Forum Discussion
Calulating a remining budget
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
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]))
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