Weighted Average function
The Weighted Average function calculates the sum of all the values multiplied by their weights, then divided by the sum of the weights.
The weighted average function applies the following weighted average calculation:
sum(table.column * weighting_table.column) / Sum(weighting_table.column )
The syntax for Weighted Average is:
<table>.<field>.WeightedAverage(<table>.<column>)
Use cases
The use cases for this function include deriving an average production or transportation cost from historic transactions weighted by different numeric attributes (production quantity, sales volume, etc.). For example, "orders.quantity.WeightedAverage(orders.cost)" would produce an average quantity weighted by cost.
If the Orders data was:
| Product | Quantity | Cost |
|---|---|---|
| a | 1 | 3 |
| a | 2 | 4 |
| a | 3 | 5 |
| b | 4 | 6 |
| b | 5 | 7 |
| b | 6 | 8 |
| c | 7 | 9 |
| c | 8 | 10 |
| c | 9 | 11 |
| c | 8 | 0 |
The results from the Weighted Average function would be:
| Product | Average quantity weighted by cost | Average quantity, for comparison |
|---|---|---|
| a | 2.166667 | 2 |
| b | 5.095238 | 5 |
| c | 8.066667 | 8 |
Last modified: Friday May 12, 2023