avgif() (aggregation function)
Learn how to use the avgif() function to return the average value of an expression where the predicate evaluates to true.
Calculates the average of expr in records for which predicate evaluates to true.
Syntax
avgif (expr, predicate)
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| expr | string | ✔️ | The expression used for aggregation calculation. Records with null values are ignored and not included in the calculation. |
| predicate | string | ✔️ | The predicate that if true, the expr calculated value is added to the average. |
Returns
Returns the average value of expr in records where predicate evaluates to true.
Example
The following example calculates the average damage by state in cases where there was any damage.
StormEvents
| summarize Averagedamage=tolong(avg( DamageCrops)),AverageWhenDamage=tolong(avgif(DamageCrops,DamageCrops >0)) by State
Output
The results table shown includes only the first 10 rows.
| State | Averagedamage | Averagewhendamage |
|---|---|---|
| TEXAS | 7524 | 491291 |
| KANSAS | 15366 | 695021 |
| IOWA | 4332 | 28203 |
| ILLINOIS | 44568 | 2574757 |
| MISSOURI | 340719 | 8806281 |
| GEORGIA | 490702 | 57239005 |
| MINNESOTA | 2835 | 144175 |
| WISCONSIN | 17764 | 438188 |
| NEBRASKA | 21366 | 187726 |
| NEW YORK | 5 | 10000 |
| … | … | … |
Related content
- Aggregation function types at a glance
- avg() (aggregation function)
- minif() (aggregation function)
- maxif() (aggregation function)
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.