Forum Discussion

Helper I

1 year ago

Solved

Detailed Budget Allocation Based on Actuals

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

Need Help

katietran0467

1 year ago

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

maruthisp

Super User

1 year ago

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