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

v-sgandrathi

Community Support

1 year ago

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.