Specify Member Weight
Aggregate weights are used when a Member in an Aggregate must be assigned a multiplied value in relation to other Members in the Aggregate in order for the data to be properly represented. For example, in the Account dimension, in order to arrive at a calculation of the Parent, Margin, Cost of Sales should always be handled as a negative number because it will always represent an amount subtracted from Revenue.
To designate Cost of Sales as a negative number in relation to the Margin hierarchy, we can assign it a weight of –1. This means that the data included in Cost of Sales will always be multiplied by –1 when it is rolled up in that Aggregate. The Margin hierarchy in the Account Dimension appears as follows:
The following steps will show how to define the Aggregate Weight of a Child member in the Sales Account dimension.
To “weight” Cost of Sales, proceed as follows:
1. Bring up the ‘Sales Account’ Hierarchy dialog box (double-click on Sales Account in the Dimensions dialog).
2. Add the new Member, <Margin>.
3. Double-click or right-click on the Member Cost of Sales and select Edit Weights from the pop-up menu; or select the Specify Member Weights toolbar button.
4. Enter -1 as the weight of Cost of Sales in the box:
5. Click the OK button (green checkmark) when complete. You are returned to the Dimension dialog box.
6. Click OK. You are returned to the main application window.
In PowerOLAP®, the default Aggregate weight is equal to ‘1’. Thus, a parent Aggregate member is simply the sum of all Child members defined in the Dimension hierarchy. In the example exercises we have just completed, all aggregates you defined are standard with the exception of Margin, in the Sales Account dimension.