Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Proud to be a Super User!
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.
Proud to be a Super User!
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]))
Proud to be a Super User!
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
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])
)
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |