Microsoft Office Tutorials and References
In Depth Information
You end up with a table that contains the sales for the fiscal year 2004. To change it to
it your needs, you rename some columns. Year becomes OriginalYear, amount becomes
OriginalAmount, and you add new columns, which are computed based on the original ones,
to move time one year forward, to which you apply the 7.5 percent increase. The only tricky
column is the month name, which requires some string acrobatics to be computed. The
formula for the month name is this:
= LEFT( OriginalSales[OriginalMonth],
FIND( " ", OriginalSales[OriginalMonth] ) )
& ( VALUE( RIGHT( OriginalSales[OriginalMonth], 4 ) + 1 )
Now you end up with a table, shown in Figure 8-26, that contains the predicted sales for the
next fiscal year. Please note that in the figure we already hid the original columns, retaining
only the predicted columns.
FIguRE 8-26 The table that contains predicted sales.
As you see, applying the corrective factor is easy. The next step is to add some rows to this
table so that you can simulate the existence of products that are not stored in the original
database.
You have a couple of options here. If the original table is a small one, such as this example, you
can simply copy the table from PowerPivot into a new Excel worksheet, update it with new data,
and then create a new linked table based on that worksheet, which in turn is the source for your
PivotTable.
Although this technique works pretty well for small amounts of data, it has the disadvantage
of being inappropriate when the number of rows grows too much because you can easily end
up with very big workbooks. (Remember that Excel files with millions of rows are slow and big.)
Moreover, if the original data changes for some reason, updating it with new data becomes
difficult because you must perform a complex process to re-create the merged table.
Search JabSto ::




Custom Search