Detailed Budget Allocation Based on Actuals

Hi katietran0467,

Thankyou for sharing the details and DAX formulas you’re very close, and your objective is clear that allocate the monthly budget proportionally to actuals at the detailed level, even when filters are applied.

Try using this Updated DAX Measure:

_TotalActualPerPeriod =

CALCULATE(

    SUM('Fact'[Amount]),

    REMOVEFILTERS(Account),

    'Fact'[Type] = "Debit"

)

_TotalBudgetPerPeriod =

CALCULATE(

    SUM('Fact'[Amount]),

    REMOVEFILTERS(Account),

    'Fact'[Type] = "Credit"

)

_BudgetPerActual =

DIVIDE([_TotalBudgetPerPeriod], [_TotalActualPerPeriod])

_DetailedBudget =

VAR _IsSingle = HASONEVALUE(Account[AccountName])

RETURN

    IF(

        _IsSingle,

        SUM('Fact'[Amount]) * [_BudgetPerActual],

        SUMX(

            VALUES(Account[AccountName]),

            CALCULATE(SUM('Fact'[Amount]) * [_BudgetPerActual])

        )

    )

I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.

Thank you.

Hi 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.

Hi katietran0467 ,

Please find the below DAX expressions to solve the issue.
Hope you have Fact table with Fact[Type] with values Actual and Budget
Fact[YearMonth]

Let's calculate the total actuals per period:
TotalActuals_Period :=
CALCULATE(
SUM( Fact[Amount] ),
Fact[Type] = "Actual",
ALL( Fact[Type] ) // ignore any Type filter so we always get full Actuals for the period
)

After that, calculate Total Budget per period:
TotalBudget_Period :=
CALCULATE(
SUM( Fact[Amount] ),
Fact[Type] = "Budget",
ALL( Fact[Type] ) // same trick to grab full Budget for period
)

Now calculate Budget-to-Actual ratio:
BudgetPerActual :=
DIVIDE(
[TotalBudget_Period],
[TotalActuals_Period]
)

Now to spread the monthly budget down to each detailed row in proportion to its actual amount.
Detailed Budget Allocation:
AllocatedBudget :=
SUMX(
VALUES( Fact[RecordID] ), // or any row-level granularity
Fact[Amount] * [BudgetPerActual]
)

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

Hi katietran0467,

Thank you maruthisp for your reply on the query.

Has your issue been resolved?If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

Hi,

Share some data to work with and show the expected result very clearly.  Share data in a format that can be pasted in an MS Excel file.