  Data Analysis and Reporting with Intelligence Clients

## Using Weighted Functions

Weighted averages can be very useful for a variety of purposes, such as survey research or when you want to include demographic information in your pivot tables. For example, assume you took a survey of 100 people, 75 male and 25 female. But according to census data in that geographic region you should have surveyed 50 males and 50 females. The data you have is skewed toward males.

To correct for this, you assign a weight or weighting factor to correct for the sampling error in your survey. To calculate a weight you take the expected amount and divide it by the actual amount.

In the example, the men would have a weighting factor of: The women would have a weight of: Any calculation would calculate each man as 0.6666 and each woman as 2.

Weighted averages can also be used to apply different levels of importance to a given item. Take, for example, a survey, which has multiple questions. The responses can be rated on a scale of 1 to 5. By assigning a weight to each question based on the level of importance (the higher the number the more important), and using that weight in calculating a weighted average, you can arrive at averages that are more meaningful.

To use weighted averages, you must add a column of data to the database. This data indicates the relative weight of each corresponding value in another column. The statistical calculation for weighted averages depends on the following mathematical formula: Weighted Values Example

Customer ID
Units Sold
Type of Store
Weight
1435
80
Electronics
8
1539
200
Computer
10
1634
60
Electronics
8
1213
900
Discount
2

To use weighted averages:

1. Ensure that a column of data with the weighted values exists in the database.
2. In the Query section, select the Topic item for which weighted values are needed.
3. Choose Query Data Functions Weight.
4. The Reference dialog box appears.

5. Select the item that contains the weighted values and click OK.
6. The item in the Request line is renamed to indicate it is a weighted value.

7. Process the query.
8. The weighted values are returned in the Results section.

9. Go to the Pivot section and drag the Weighted item from the Catalog pane to the Outliner.
10. You may now use all of the various data functions on the weighted values.   Note   Weighting functions work only in the Pivot section.  