Is there any way to create a weighted average measure?


I saw this help page:

but I’m not sure it’s exactly what I’m looking for. I may be mistaken but I think the link above doesn’t actually show a weighted average; it should be dividing by the sum of the weights instead of just taking the straight average I believe.

(peter.whitehead) #2

Hi @Steven_Wang!

Thank you for bringing this to our attention. I do agree with you that the weighted average should be calculated by dividing by the sum of the weights. I have put in a request to update the documentation.

But to calculate the weight properly the LookML should look like this:

First get the weights

dimension: weight {
  type: number 
      WHEN ${days_since_order} < 30 THEN 3
      WHEN ${days_since_order} < 60 THEN 2
      ELSE 1
    END ;;

Next we calculate the weighted price:

dimension: weighted_price {
  type: number
  sql: ${sale_price} * ${weight} ;;

And finally we calculate the weighted average:

 measure: weighted_average {
  type: number
  sql: sum(${weighted_price})/sum(${weight}) ;;

Please let us know if you have any other questions, cheers!


Awesome, thanks!

(Jacopo Chiapparino) #4

Depending on your database you could do this natively as well, for example with a function or extension.

There are a few available in postgres which can allow you to type considerably less (this decrease mistakes):

select weighted_average(price, quantity) from facts;

This is especially useful if your team needs to use this type of calculations a lot around various Looker views.

We use the same approach for medians and such. Bonus points if you also ship these to your warehouse with migrations and make them available to the rest of the users who query also closer to the db.