Forecasting with PoverPivot (Grand total not ending up correctly)

[AdrianM]

Hi there,

I'm trying to make a simple forecast for the rest of the year. I have Jan to Jun actuals as monthly values and would like to forecast the rest of the year Jul - Dec, as of Jun actuals for example. Therefore I'm using a disconnected table, where I would select 06 as a parameter for Jun and this would be my forecast base for the upcomming month.

It's working pretty well, however the Grand total on row level in the Pivot don't calculate properly.

Hope anybody has a good idea



measures:

Sales Measure:=sumx(Data;Data[Sales])

MonthNoMeasure:==maxx(Data;Data[MonthNo])

ParameterMeasure=if(ISFILTERED(Data[MonthNo]);max(Parameter[MonthParameter]);BLANK())

ForecastMeasure=if (Parameter[ParameterMeasure]<Data[MonthNoMeasure];
calculate(sumx(Data;Data[SalesMeasure]);filter(ALL(Data[MonthNo]);Data[MonthNo] =Parameter[ParameterMeasure]));
sumx(Data;Data[SalesMeasure]))

 

[scottsen]

Months are on row? I would expect to see some =SUMX(Calender[Month], ...) in your measures.