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
