on - edited on by
DRay
Analytical Need
Weighted average refers to the mathematical practice of adjusting the components of an average to reflect the importance of certain characteristics.
This practice can be useful in different scenarios, a few examples:
- In economics, this can be used to calculate the price of a stock when each stockholder has different shares with a different price.
- In academics, this can be used to calculate a student's grade based on exams with different importance.
- In marketing, this can be used to calculate the average of a product according to the distribution of the product with its price in different stores.
Modeling Challenge
In addition to the mathematical aspect, in many cases the relevant information for the calculation would spread over different tables.
In the discussed example, we will look at several products and calculate their average price.
A product is sold in several stores; Different stores have a different price for the same product; A store's distribution varies. Considering that, using the simple Average function would not be enough. We need to take into account how many times each price appears and calculate the average price accordingly.
Looking at a small sample data set, the structure would be similar to this:
The .ecdata and .dash files discussed in the example are attached for your convenience.
Solution
This requirement can be achieved by creating a custom formula in the dashboard.
The logic would be:
Sum of ( Price * Number of countries per store ) / Sum of ( number of countries per stores )
The formula would be written as follows:
To understand the formula's components, we can use a pivot widget to split it.
For better clarification, we will start by looking at all the relevant fields for the calculation:
- Set one field for the maximum price. The purpose is to display a single value. In case you have the price for a product in a store listed more than once, setting it to total would sum the values and return the wrong results. You can also use minimum or average instead of maximum. For more information, please review this article regarding multi-pass aggregation.
- Add a formula for max price * # of countries –
- The next step would be to sum the results and group it by the product. In the final display, we will not have the store_id to be used for grouping.
So, we will calculate the results by stores, sum them up, and group it by product, which is used as a dimension in the pivot:
- To calculate the denominator, we need to get the total number of stores, in all countries, so we can count the appearances of a specific product.
We will do so by counting all countries, per store, as follows:
- After disabling the store_id dimension and applying the formula:
After combining the entire formula:
To Demonstrate the difference between the weighted average and a standard average:
Attachments
