Getting totals correct when using the budget allocation formula

DAX DAX Calculations
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)

https://forum.enterprisedna.co/t/how-place-dax-formula-into-forum-topics-posts/156/3

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

1 Like
4

image1
image1.png1928×1115 149 KB

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

image
image.png1186×770 57.5 KB

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.