Hi Clive, Thank you so much for your response. It is very helpful!
I also have column with weeks in my table, how can i calculate weighted_avg within each week? In excel i usually do it using IF clause logic: SUMPRODUCT(--(weeks!$C:$C=$A6),weight!$I:$I,value!$G:$G)/SUMIF(weeks!$C:$C,$A6,weight!$I:$I)
How about this? The screenshot really helped, however you could have pasted the query and a line of data into this message as text, to save anybody having to re-type it, copy & paste is easier :) This just helps us to help you faster, especially on a complex query or dataset. I often add a line at the end of a test query "| limit 1", I can then copy the results of one line and column headings
// = (20 + 40 + 40 + 90 + 90 + 90) / 1 + 2 + 3
let t = datatable (Value:int, Weight:int, Week:string)
| extend sumvalue = (Value * Weight)
| summarize weighted_avg = (sum(sumvalue) / sum(Weight) ) by Week