Forum Discussion
Helper IDetailed Budget Allocation Based on Actuals
Hi everyone,
I figured out the solution based on the concept of SQL view. I created a temporary view to be able to use SUMX.
Here is the final measure. Hope it helps other members or me in the future.
_DetailBudget =
var _table = FILTER(
SUMMARIZECOLUMNS(
'Month'[YearMonth], Account[Account], "Amount", MIN('Fact'[Amount]),
"_Multipler", DIVIDE(CALCULATE(sum('Fact'[Amount]), 'Fact'[Type] <> "Debit", ALL('Account')), CALCULATE(sum('Fact'[Amount]), 'Fact'[Type] <> "Credit", ALL('Account'))),
"_New", MIN('Fact'[Amount]) * DIVIDE(CALCULATE(sum('Fact'[Amount]), 'Fact'[Type] <> "Debit", ALL('Account')), CALCULATE(sum('Fact'[Amount]), 'Fact'[Type] <> "Credit", ALL('Account')))
)
,
Account[Account] <> "Budget")
RETURN
SUMX(_table, [_New])
Super UserHi katietran0467 ,
This type of proportional budget allocation based on actuals is a common challenge in Power BI, and it’s easy to run into issues with totals or visuals when filter context gets tricky. Here’s the approach that’s worked reliably for me in production models:
Step 1: Calculate total actuals and total budget for the period (ignoring account filter)
_TotalActualsPeriod =
CALCULATE(
SUM(Fact[Amount]),
Fact[Type] = "Debit",
REMOVEFILTERS(Account)
)
_TotalBudgetPeriod =
CALCULATE(
SUM(Fact[Amount]),
Fact[Type] = "Credit",
REMOVEFILTERS(Account)
)
Step 2: Work out the ratio of budget to actuals
_BudgetPerActual =
DIVIDE([_TotalBudgetPeriod], [_TotalActualsPeriod])
Step 3: Allocate budget to each actual at the detailed level
_DetailedBudget =
SUMX(
FILTER(Fact, Fact[Type] = "Debit"),
Fact[Amount] * [_BudgetPerActual]
)
If you have a more complex account structure or need to handle subtotals cleanly, consider wrapping your allocation logic in an ISINSCOPE(Account[AccountName]) check, but for most scenarios the above will do the trick.
katietran0467Thanks Rohit for your reply.
I applied your revised measure but still returned the incorrect total figures.