Forum Discussion
Solved
Weighted average in Azure Log Analytics
Hi everybody, I'm new in Azure Log Analytics /KQL/ Language that AppInsights uses How can i calculate weighted average? Thank you
- this is super weird. i had done the same thing with 'by Week' stuff before I replied to you.
and It didn't work. there was some mistake with 'By'.
and now it works! ha-ha
Thank you, Clive! You helped me a lot!
// = (20 + 40 + 40 + 90 + 90 + 90) / 1 + 2 + 3
let t = datatable (Value:int, Weight:int)
[
20,1,
40,2,
90,3
];
t
| extend sumvalue = (Value * Weight)
| summarize weighted_avg = (sum(sumvalue) / sum(Weight) )
// = 61
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)
Can do it somehow like this in AppInsights?
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)
Can do it somehow like this in AppInsights?
Hello,
Its always easier if you can provide the actual data or an example, what format is the "weeks" column in, is it a timestamp, a week number etc....
Would my datatable look like this?
let t = datatable (Value:int, Weight:int, Week:string)[20,1,"Week1",40,2,"Week2",90,3,"Week3",];I assume you want to see a line that says:Project <weighted avg>, <week name or number>i can provide screenshot, if it helps
i'm trying to analyzing response time here
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 + 3let t = datatable (Value:int, Weight:int, Week:string)[10,1,"2019-02-25T00:00:00.000",10,1,"2019-02-26T00:00:00.000",20,1,"2019-02-25T00:00:00.000",40,2,"2019-02-25T00:00:00.000",90,3,"2019-02-26T00:00:00.000",];t| extend sumvalue = (Value * Weight)| summarize weighted_avg = (sum(sumvalue) / sum(Weight) ) by Week
