Microsoft Office Tutorials and References
In Depth Information
It is beyond the scope of this topic to discuss the details of Microsoft Visual Basic for Applications
(VBA) code of the macro, but this simple example iterates over all the rows in the PriceBands
original table (the outer loop) and then, for each row, it generates rows in the PriceBandsExpanded
table with the values (inner loop).
The two tables are shown side by side in Figure 10-4, where you also can see the Compute
button that is linked to the macro shown previously.
FIguRE 10-4 The original and expanded price band tables.
Now you can import the new PriceBandsExpanded table as a PowerPivot linked table, and
you can use the Price column of the new table as the key and the PriceBand column to
slice sales. Because the DiscountedPrice in the Sales table is a floating number, you need
to define a calculated column to round it to the nearest integer value, using one of the
many rounding functions available in PowerPivot. Last, you can create the relationship,
as you can see in Figure 10-5.
FIguRE 10-5 The relationship between the sales and price bands expanded table.