Detailed Budget Allocation Based on Actuals

[katietran0467]

Hi everyone,

I'm facing a challenge with calculating budget allocations based on actual data.

Here's the situation:
We have actual figures at a detailed level, but only have monthly budget totals for the entire dataset. My goal is to distribute the monthly budget down to the detailed level in proportion to actuals.

I'm trying to calculate a detailed-level budget using actuals as a basis, but the total shows incorrectly or disappears when filtering.

_TotalActualPerPeriod = CALCULATE(
    SUM('Fact'[Amount]),
    Account[Account] <> "Budget",
    ALL(Account)
)

_TotalBudgetPerPeriod = CALCULATE(
    SUM('Fact'[Amount]),
    Account[Account] = "Budget",
    ALL(Account)
)

_BudgetPerActual = DIVIDE([_TotalBudgetPerPeriod], [_TotalActualPerPeriod])

_DetailedBudget = 
VAR _table = SUMMARIZE(
    'Fact', 
    'Fact'[YearMonth], 
    'Fact'[Account], 
    "_value", SUM('Fact'[Amount]) * [_BudgetPerActual]
)
RETURN
IF(
    HASONEVALUE(Account[AccountName]), 
    SUM('Fact'[Amount]) * [_BudgetPerActual], 
    SUMX(_table, [_value])
)

Any suggestions or feedback on how to improve this logic or approach the problem differently are greatly appreciated.

Thank you!

[katietran0467 · Accepted solution]

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])

[Ashish_Excel]

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.

[katietran0467]

Hi @Ashish_Excel 

Here is the data

YearMonthAccountAmountType

202501	HR	23	Debit
202501	IT	15	Debit
202501	BD	54	Debit
202501	Budget	100	Credit
202502	HR	26	Debit
202502	IT	19	Debit
202502	BD	48	Debit
202502	Budget	95	Credit
202503	HR	17	Debit
202503	IT	36	Debit
202503	BD	45	Debit
202503	Budget	89	Credit

And here is the expected outcome (red total figure)

[v-sgandrathi]

Hi @katietran0467,

Thank you for sharing the data and the expected outcome.
I'm attaching the .pbix file, please have a look and let me know if this aligns with what you were expecting.

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.

[katietran0467]

Hi @v-sgandrathi 

Thanks for your time to investigate it.

I checked your file but the total number is still incorrect. The DetailedBudget should be equal to TotalBudget = 284 in total, not 569 as your file.

To work around, I created a view by SUMMARIZECOLUMN and put numbers to use SUMX.

If there is a way using DAX calculation without creating a view, it would be a better solution.

[Ashish_Excel]

Cannot understand anything there.

[v-sgandrathi]

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!

[katietran0467]

Hi @v-sgandrathi 

Unfortunately, no responses from community member address my query. One of them has a syntax issue and others are quite similar to my initial DAX.

I wonder if these reponses were tested in Power BI before posted?

[maruthisp]

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

[katietran0467]

Hi @maruthisp 

Your DAX does not work and they have the syntax issue.

Thanks.

[rohit1991]

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.

[katietran0467]

Thanks Rohit for your reply.

I applied your revised measure but still returned the incorrect total figures.

[v-sgandrathi]

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.

[katietran0467]

Thank your for your reply and provided solution.

I have tried SUMX before using the SUMMARIZE but it still returned the incorrect total as below.