• 507K Members
• 3,474 Online
• 603K Conversations
SOLVED

## Weighted average in Azure Log Analytics

Occasional Contributor

# 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

7 Replies

# Re: Weighted average in Azure Log Analytics

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

# Re: Weighted average in Azure Log Analytics

Hi Clive,

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?
Highlighted

# Re: Weighted average in Azure Log Analytics

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>

# Re: Weighted average in Azure Log Analytics

i can provide screenshot, if it helps

i'm trying to analyzing response time here

# Re: Weighted average in Azure Log Analytics

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

# Re: Weighted average in Azure Log Analytics

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!

# Re: Weighted average in Azure Log Analytics

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

Related Conversations
Powershell Script for Vault details.
Prashant Sharma in Azure on
2 Replies
updating my OMS queries
vicky2019 in Azure on
3 Replies
Azure application gateway accesslogs query - Timetaken
Vinoth_Azure in Azure on
3 Replies