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 ,

You're on the right track with your approach to allocate monthly budget totals proportionally to detailed actuals. The issue you're facing—where the total budget allocation disappears or displays incorrectly under filters—is likely due to how your _DetailedBudget measure is handling context, especially the use of HASONEVALUE and how SUMX is being evaluated.

A cleaner and more reliable approach would be to explicitly isolate actuals and budget using variables and apply the proportional allocation using CALCULATE in a way that preserves row context without being affected by slicers or filters. Here's a revised version of your measure:

_DetailedBudget = 
VAR _actualTotal = CALCULATE(
    SUM('Fact'[Amount]),
    'Fact'[Type] = "Debit", 
    REMOVEFILTERS(Account)
)
VAR _budgetTotal = CALCULATE(
    SUM('Fact'[Amount]),
    'Fact'[Type] = "Credit", 
    REMOVEFILTERS(Account)
)
VAR _actual = SUM('Fact'[Amount])
VAR _allocationRatio = DIVIDE(_budgetTotal, _actualTotal, 0)
RETURN 
_actual * _allocationRatio

This version uses the Type column (Debit/Credit) to separate actuals and budget without relying on string matching from a dimension, which avoids filtering issues. REMOVEFILTERS(Account) ensures you’re summing across the full account set per month, maintaining a consistent base for allocation even when a single account is selected.

Finally, if you still face total-level inconsistencies in matrix visuals, consider using ISINSCOPE() instead of HASONEVALUE() to better manage nested contexts in visuals. This should provide accurate row-level budget allocations and reliable totals even under filters.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

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