Microsoft Office Tutorials and References
In Depth Information
FIguRE 10-3 The Excel table that defines price bands.
It might occur to you to import the price bands table into a PowerPivot linked table, make a
relationship between this new table and the sales, and then slice sales by price band. As easy
as this solution seems, it cannot work. You need to base the relationship on the price range,
not on a single key. In fact, there is no column in the sales table that can act as a key for the
price band: the discounted price, $28.50, for example, does not point to any row in the bands
table. We definitely need some advanced technique to handle banding because standard
PowerPivot relationships are not enough.
Banding with Band Expansion
A possible solution to the banding problem is to create a new price band table that contains the
price band for every single price and then use the DiscountedPrice in the sales table as the key to
get into this new table. So if a price band ranges from $1.00 to $10.00 USD, you can create a new
table containing 10 rows, one for each price, and each one with the same band description.
Clearly, with ranges varying from $1.00 to $2,500.00 USD, this new table contains 2,500 rows, and
we do not even think about filling it by hand; we rely on Excel macros to perform this tedious task.
You can define a new table in the same workbook and name it PriceBandsExpanded, in which
the macro loads the rows of the new table. The code of the macro might look like this:
Sub Button1_Click()
Dim PriceBandsExpanded As ListObject
Set PriceBandsExpanded = ActiveSheet.ListObjects("PriceBandsExpanded")
Application.ScreenUpdating = False
If Not (PriceBandsExpanded.DataBodyRange Is Nothing) Then
PriceBandsExpanded.DataBodyRange.Delete
End If
For Each Row In ActiveSheet.ListObjects("PriceBands").ListRows
Dim MinValue As Integer
Dim MaxValue As Integer
Dim Value As Integer
Dim newRow As ListRow
MinValue = Row.Range(1, 2).Value
MaxValue = Row.Range(1, 3).Value - 1
For Value = MinValue To MaxValue
Set newRow = PriceBandsExpanded.ListRows.Add
newRow.Range(1, 1) = Row.Range(1, 1)
newRow.Range(1, 2) = Value
Next
Next
Application.ScreenUpdating = True
End Sub
Search JabSto ::




Custom Search