Getting totals correct when using the budget allocation formula


#1

I’m working through the budgeting module and having problems summing up the column for the following measure “Budget Allocation w/weighting” to showcase the total as a card. I’m sure it’s an easy fix, any suggestions?

Thanks

Budget Allocation w/Weighting =
VAR BudgetWeights = IF( HASONEVALUE( Dates[Month Name] ) ,
CALCULATE( SUM( 'Budget Allocation'[Monthly Weighting] ),
               FILTER( 'Budget Allocation', 'Budget Allocation'[Month Name] = VALUES( Dates[Month Name] ) )),
BLANK() )
VAR DaysInMonth = CALCULATE( COUNTROWS( Dates ),
               ALL( Dates[Date] ),
               VALUES( Dates[Month in Year] ) )
 
RETURN
DIVIDE(BudgetWeights, DaysInMonth, 0) * [Total Budget]

#2

Hi Selim,

Just couple of thing before I forget. Check out this video on how to format formula in the forum (I updated this time and seemed to work)

Also can you place an image of the results your are getting and what’s is missing?

Thanks
Sam


#3

The answer will be based on something like this I would say

Just need full context of how this is being calculated on your report first


#4


#5

Thanks Sam,
I’ll check out the video.


#6

Try this. There’s a bit to this one.

But this gets the total exactly based on the date context within a filter.

Budget Allocation w/Weighting = 
VAR FilterCheck = OR( HASONEVALUE( Dates[Date] ), HASONEVALUE( Dates[Month & Year] ) )
VAR CurrentMonth = SELECTEDVALUE( Dates[MonthName], BLANK() )
VAR BudgetWeight =  CALCULATE( MIN( 'Budget Allocations'[Monthly Weighting] ), FILTER( 'Budget Allocations', 'Budget Allocations'[Month Name] = CurrentMonth ) )
VAR BudgetFactor = DIVIDE( COUNTROWS( Dates ), CALCULATE( COUNTROWS( Dates ), ALL( Dates[Date] ), VALUES( Dates[Month & Year] ) ) )
VAR BudgetAllocation = [Total Budget] * ( BudgetWeight * BudgetFactor )

RETURN
IF( FilterCheck,
    BudgetAllocation,
        [Total Budget] * ( [Days in Date Context] / [Days in Year] ) )


#7

Thanks Sam, works like a charm. I would have never figured that one out.


#8

Yes there was a bit more too it than first thought, especially in always getting the right total for the current context selected.

Lot’s of learning with just this one formula though.