SOLVED
Home

Weighted average in Azure Log Analytics

%3CLINGO-SUB%20id%3D%22lingo-sub-358247%22%20slang%3D%22en-US%22%3EWeighted%20average%20in%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358247%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everybody%2C%3CBR%20%2F%3EI'm%20new%20in%20Azure%20Log%20Analytics%20%2FKQL%2F%3CBR%20%2F%3ELanguage%20that%20AppInsights%20uses%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20i%20calculate%20weighted%20average%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-358247%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EApplication%20Insights%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358925%22%20slang%3D%22en-US%22%3ERe%3A%20Weighted%20average%20in%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358925%22%20slang%3D%22en-US%22%3E%3CP%3EExcellent%2C%20I'm%20pleased%20this%20is%20working%20for%20you%20now.%26nbsp%3B%20All%20the%20best%2C%20Clive%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358812%22%20slang%3D%22en-US%22%3ERe%3A%20Weighted%20average%20in%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358812%22%20slang%3D%22en-US%22%3Ethis%20is%20super%20weird.%20i%20had%20done%20the%20same%20thing%20with%20'by%20Week'%20stuff%20before%20I%20replied%20to%20you.%3CBR%20%2F%3Eand%20It%20didn't%20work.%20there%20was%20some%20mistake%20with%20'By'.%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20now%20it%20works!%20ha-ha%3CBR%20%2F%3EThank%20you%2C%20Clive!%20You%20helped%20me%20a%20lot!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358803%22%20slang%3D%22en-US%22%3ERe%3A%20Weighted%20average%20in%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358803%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20about%20this%3F%26nbsp%3B%20%3CBR%20%2F%3EThe%20screenshot%20really%20helped%2C%20however%20you%20could%20have%20pasted%20the%20query%20and%20a%20line%20of%20data%20into%20this%20message%20as%20text%2C%20to%20save%20anybody%20having%20to%20re-type%20it%2C%20copy%20%26amp%3B%20paste%20is%20easier%20%3A)%3C%2Fimg%3E%26nbsp%3B%20This%20just%20helps%20us%20to%20help%20you%20faster%2C%20especially%20on%20a%20complex%20query%20or%20dataset.%26nbsp%3B%20%26nbsp%3BI%20often%20add%20a%20line%20at%20the%20end%20of%20a%20test%20query%20%22%3CSTRONG%3E%7C%20limit%201%3C%2FSTRONG%3E%22%2C%20I%20can%20then%20copy%20the%20results%20of%20one%20line%20and%20column%20headings%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSTRONG%3E%2F%2F%20%3D%20(20%20%2B%2040%20%2B%2040%20%2B%2090%20%2B%2090%20%2B%2090)%20%2F%201%20%2B%202%20%2B%203%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3Elet%20t%20%3D%20datatable%20(Value%3Aint%2C%20Weight%3Aint%2C%20Week%3Astring)%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E%5B%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E10%2C1%2C%222019-02-25T00%3A00%3A00.000%22%2C%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E10%2C1%2C%222019-02-26T00%3A00%3A00.000%22%2C%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E20%2C1%2C%222019-02-25T00%3A00%3A00.000%22%2C%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E40%2C2%2C%222019-02-25T00%3A00%3A00.000%22%2C%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E90%2C3%2C%222019-02-26T00%3A00%3A00.000%22%2C%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E%5D%3B%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3Et%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E%7C%20extend%20sumvalue%20%3D%20(Value%20*%20Weight)%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E%7C%20summarize%20weighted_avg%20%3D%20(sum(sumvalue)%20%2F%20sum(Weight)%20)%20by%20Week%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358797%22%20slang%3D%22en-US%22%3ERe%3A%20Weighted%20average%20in%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358797%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20can%20provide%20screenshot%2C%20if%20it%20helps%3C%2FP%3E%3CP%3Ei'm%20trying%20to%20analyzing%20response%20time%20here%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358794%22%20slang%3D%22en-US%22%3ERe%3A%20Weighted%20average%20in%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358794%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIts%20always%20easier%20if%20you%20can%20provide%20the%20actual%20data%20or%20an%20%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fukhybridcloud%2F2018%2F09%2F12%2Fazure-log-analytics-do-you-need-some-demo-data%2F%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Eexample%3C%2FA%3E%2C%20what%20format%20is%20the%20%22weeks%22%20column%20in%2C%20is%20it%20a%20timestamp%2C%20a%20week%20number%20etc....%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWould%20my%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fdatatableoperator%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Edatatable%3C%2FA%3E%20look%20like%20this%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSTRONG%3Elet%20t%20%3D%20datatable%20(Value%3Aint%2C%20Weight%3Aint%2C%20Week%3Astring)%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E%5B%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E20%2C1%2C%22Week1%22%2C%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E40%2C2%2C%22Week2%22%2C%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E90%2C3%2C%22Week3%22%2C%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3E%5D%3B%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EI%20assume%20you%20want%20to%20see%20a%20line%20that%20says%3A%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EProject%20%3CWEIGHTED%3E%2C%20%3CWEEK%20name%3D%22%22%20or%3D%22%22%20number%3D%22%22%3E%3C%2FWEEK%3E%3C%2FWEIGHTED%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358790%22%20slang%3D%22en-US%22%3ERe%3A%20Weighted%20average%20in%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358790%22%20slang%3D%22en-US%22%3EHi%20Clive%2C%3CBR%20%2F%3EThank%20you%20so%20much%20for%20your%20response.%20It%20is%20very%20helpful!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20also%20have%20column%20with%20weeks%20in%20my%20table%2C%20how%20can%20i%20calculate%20weighted_avg%20within%20each%20week%3F%3CBR%20%2F%3EIn%20excel%20i%20usually%20do%20it%20using%20IF%20clause%20logic%3A%3CBR%20%2F%3ESUMPRODUCT(--(weeks!%24C%3A%24C%3D%24A6)%2Cweight!%24I%3A%24I%2Cvalue!%24G%3A%24G)%2FSUMIF(weeks!%24C%3A%24C%2C%24A6%2Cweight!%24I%3A%24I)%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20do%20it%20somehow%20like%20this%20in%20AppInsights%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358343%22%20slang%3D%22en-US%22%3ERe%3A%20Weighted%20average%20in%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358343%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20%3D%20(20%20%2B%2040%20%2B%2040%20%2B%2090%20%2B%2090%20%2B%2090)%20%2F%201%20%2B%202%20%2B%203%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3Elet%20t%20%3D%20datatable%20(Value%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Eint%3C%2FSPAN%3E%3CSPAN%3E%2C%20Weight%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Eint%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E20%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E40%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E90%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%5D%3B%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3Et%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20sumvalue%20%3D%20(Value%20*%20Weight)%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20weighted_avg%20%3D%20(sum(sumvalue)%20%2F%20sum(Weight)%20)%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%2F%2F%20%3D%2061%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Highlighted
elza_ts
Occasional 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
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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies