Amazon QuickSight – avgIf

The avgIf function in Amazon QuickSight is an aggregate function that calculates the average of a given dimension or measure, but only for those rows that meet a specified condition.

Syntax

#Start#
ceil(decimal)
#End#

This function takes two input parameters, a dimension or measure, and a condition that specifies which rows to include in the calculation.

1. dimension-or-measure: The dimension or measure to calculate the average for. You can use column names, constants, or other expressions as inputs.
2. condition: The condition that specifies which rows to include in the calculation. The condition is expressed as an expression that evaluates to either true or false. You can use column names, constants, or other expressions as inputs.

Suppose you have a dataset that contains information about a company’s sales in different regions. 

The dataset has the following columns

• Region: The name of the region where the sales were made.
• Sales: The total sales for each region.
• Year: The year in which the sales were made.

To calculate the average sales for the East region in the year 2021, you could use the following expression.

Example

#Start#
avgIf({Sales}, {Region} = "East" and {Year} = 2021)
#End#

This expression calculates the average of the Sales column, but only for those rows where the Region column is equal to “East” and the Year column is equal to 2021. The result would be a single number that represents the average sales for the East region in 2021.

For example, if the dataset contains the following data

The result of the expression would be the value 200, which represents the average sales for the East region in 2021. This is because there is only one row in the dataset that meets the specified condition: the row where the region is East and the year is 2021. The avgIf function calculates the average of the Sales column for that row, which is 200.