When to Use a Weighted Average

Weighted averages are a good metric type to use when a variable in the data needs to be given more importance (i.e. weight) than other variables. They are also good to use to check for the degree of bias a factor has or to correct for a bias you know exists.

Example Situation

You want to know the average cost of carrying an item of inventory for a specific brand.

You may be tempted to filter to the brand and compute the average inventory item cost, like this:

Yes, the individual item prices average out to $54.69, but there are 20 items in inventory. Since the question is about the average cost of carrying a brand in inventory, not about the average cost to buy an item from a brand, a weighted average will give a more accurate answer.

Goal

Create a weighted average field in order to more accurately represent information and incorporate confounding factors into your analysis.

What is a weighted average?

A straight average (aka average aka mean) is when you take the sum of a set of numbers and then divide by the count of how many numbers are in the set in order to get a rough understanding of the middle number of the set.

A weighted average, however, takes into account that the things you are averaging can have a characteristic about what you're counting that should be taken into account (a weight). For the example in this post it is relevant that the cost of carrying a single item for a brand is influenced by which brand item sells more (and therefore requires more inventory) and hence requires a weighted average in order to answer the question.

Let me go through a simpler fictional example: Kenmore makes the following products with the following wholesale costs: a $300 dishwasher and a $30 toaster. A single retail location sells 4 dishwashers a month and 300 toasters. (We'll forget varying market COGS, bulk discounts, FIFO/LIFO, and carrying inventory surplus to what you expect to sell for the sake of simplicity.)

If you then use this measure in an Explore, you get the weighted average answer of $55.99.

Why this method

This method takes advantage of existing Looker functionality and keeps the formula in LookML where it is version controlled instead of relying on users to implement the formula correctly as a table calculation.