Incorrect Budget Allocation w/Weighting


#1

Hi Sam,
My formula for Plan Sales Allocation w.Weightings (Plan Sales is same as Budget) is giving me incorrect results.
Here is what my formula looks like

Plan Sales Allocation w.Weightings = 
VAR
	DaysinDateContext = COUNTROWS(FILTER('Date', 'Date'[Holidays & WorkDays] = "Work Day"))   	
VAR
	DaysinMoth = CALCULATE(COUNTROWS(FILTER('Date','Date'[Holidays & WorkDays] = "Work Day")), ALL( 'Date' ), VALUES( 'Date'[FY Month & FY Year] ))
VAR
    Seasonality = CALCULATE(SUM('Sales Plan Weightings'[Rates]), TREATAS(VALUES('Date'[Short Month]), 'Sales Plan Weightings'[Short Month]))
VAR    
    TotalBudget = CALCULATE(SUM('Plan Sales Data'[Plan Sales]), TREATAS(VALUES('Date'[Fiscal Year]), 'Plan Sales Data'[FY Year])) * Seasonality
   RETURN
(DaysinDateContext / DaysinMoth) * TotalBudget

I get correct results for Sales Plan Allocation but when I try to incorporate seasonality calculation it gives slightly lower totals for each month. Can you please help me with this.

Plan Sales Allocation = [Days in Date Context]/[Days in Year] * [Total Sales Plan]

I don’t think that should be the issue but the only difference in my model is that Sales Plan Weightings (same as Budget Weightings in your model) is a calculated table. Here is the calculation.

Sales Plan Weightings = ADDCOLUMNS(VALUES('Date'[Short Month]), "Rates", CALCULATE(DIVIDE([Sales Seasonality Trend for LY] + [Sales Seasonality Trend for 2YeasAgo] + [Sales Seasonality Trend for 3YeasAgo], 3,0) + [Sales Trend Adjustment]) , "Without Adjustment", [Weightings forcast for TY])
image


#2

Where are you adding the seasonality calculation?

Also what results are you getting for the Plan Sales Allocation. You’re not showing them so it’s difficult to tell.

Need more information. Thanks


#3

Hi Sam,

Sorry, I should’ve included more information. Seasonality calculation is coming from an individual table.
I used this calculation to come up with the Rate calculation. Link
Here is the sample data screenshot. Even it shows totals correct but the numbers for each month don’t add up to that.
I get the correct results for Plan Sales Allocation until I include rates (Seasonality) it doesn’t give me correct calculation.

Here is the formula:
    Plan Sales Allocation w.Weightings = 
    VAR
    	DaysinDateContext = COUNTROWS(FILTER('Date', 'Date'[Holidays & WorkDays] = "Work Day"))
    VAR
    	DaysinYear = CALCULATE(COUNTROWS(FILTER('Date','Date'[Holidays & WorkDays] = "Work Day")), ALL( 'Date' ), VALUES( 'Date'[Fiscal Year]) )
    VAR
        Seasonality = CALCULATE(SUM('Sales Plan Weightings'[Rate]), TREATAS(VALUES('Date'[Short Month]), 'Sales Plan Weightings'[Short Month]))
    VAR    
        TotalBudget = [Total Sales Plan] * Seasonality
    RETURN
    (DaysinDateContext / DaysinYear) * TotalBudget


#4

Yes ok. So the issue looks like in the total the seasonality evaluates to the number 1, so it doesn’t factor in all the individual months and then adds them up

In the RETURN section you need a different formula.

Something like the below

SUMX(
SUMMARIZE( Dates, Short Month Column, Fiscal Year Column,
“Sales w.Weighting”, (DaysinDateContext / DaysinYear) * TotalBudget ),
[Sales w.Weighting] )

Something like this.

I can give you exact because I can’t replicate exactly myself, but this is the idea.

Here’s another example of how to do something like this.

And another one here.

The key is to think deeply about what the formula is evaluating without any context. That is what happens with any total.

See how you go.


#5

Hi Sam, I am trying to get correct numbers for each months. I am not too worried about the totals as my calculation going to show a monthly Sales Plan.
I was able to distribute my FY Plan sales into each month but I am struggling to include the weight rate into the calculation.
Exactly like the way you have done in your Dealing with Budget Allocations file. For some reason my numbers are coming out incorrect.

Here is the screen shot of your file.


#6

I would have to see and test it unfortunately.

I’ve read this numerous times and am not sure based on the information I see.

What I would recommend here, and this is what I always do myself, is break out each different part of the formula into there own measure. Then you can see exactly which part of the formula is playing up.

Sometimes when you use variables it’s hard to know all the intermediary calculations that are going on. That’s why breaking them out one by one and testing what they are calculating is the best course of action on these.