Microsoft Office Tutorials and References

In Depth Information

Note
You can rename a table in Excel from the Design tab on the Table Tools contextual tab of

the ribbon, to arrive at the Table Name text box that is displayed in the Properties group.

At this point, the missing link is a relationship between the Sales and the Prediction tables

so that you can use the predictive factor in the Sales table. You can do that in PowerPivot

by clicking the Create Relationship button and configuring it as it is shown in Figure 5-28.

FIguRE 5-28
The relationship between the Sales and Prediction tables.

Now you can use PowerPivot relationships to define two new columns in the Sales table;

you can call them PredictiveFactor and PredictedSales. The formulas, shown in Table 5-1,

are pretty easy.

TABLE 5-1
Formulas to predict sales in 2005 based on 2004 data.

Column

Formula

PredictiveFactor

=RELATED(Prediction[Increase])

PredictedSales

=Sales[SalesAmount]*(1+Sales[PredictiveFactor])

The PredictiveFactor column computes the increase in sales of the month, by using the

RELATED function. The PredictedSales column uses the predictive factor to compute the

value of sales, correcting in this way the sales in 2004 with the predictive factor.

It is now very easy to create a PivotTable based on the PowerPivot data model, add it to a

worksheet, and produce the report shown in Figure 5-29.