SOLVED

Weighted average in Azure Log Analytics

Copper Contributor

Hi everybody,
I'm new in Azure Log Analytics /KQL/
Language that AppInsights uses

How can i calculate weighted average?

 

Thank you

7 Replies

// = (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?

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 + 3
let 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
best response confirmed by CliveWatson (Microsoft)
Solution
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!

Excellent, I'm pleased this is working for you now.  All the best, Clive

 

1 best response

Accepted Solutions
best response confirmed by CliveWatson (Microsoft)
Solution
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!

View solution in original post