• 549K Members
• 3,965 Online
• 656K Conversations
SOLVED

## Weighted average in Azure Log Analytics

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

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

# 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>

Highlighted

# Re: Weighted average in Azure Log Analytics

i can provide screenshot, if it helps

i'm trying to analyzing response time here

Highlighted

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