/    /  Amazon QuickSight – sumIf

Amazon QuickSight – sumIf

 

The sumIf() function in Amazon QuickSight is used to calculate the sum of a measure in a dataset, based on certain conditions. It allows you to sum only the values that meet a specific condition or set of conditions.

 

Syntax

#Start#
sumIf(measure, conditions)
#End#

 

This function takes the following arguments:

 

  • measure: This parameter specifies the measure for which you want to calculate the sum. The measure can be a field or an expression.
  • conditions: This parameter specifies the conditions that must be met for the values to be included in the sum. It can be a single condition or a combination of conditions.

 

Suppose you have a sales dataset with the following fields: Date, Product, Sales, and Region. You want to calculate the sum of sales for the products that belong to the ‘Electronics’ category. Here’s an example formula:

 

Example

#Start#
sumIf(Sales, Product = 'Electronics')
#End#

 

This formula calculates the sum of the Sales field only for the rows where the Product field equals ‘Electronics’. The Product = ‘Electronics’ condition is used to filter the data and only include the rows where the product is ‘Electronics’.

 

You can also use multiple conditions to filter the data. For example, suppose you want to calculate the sum of sales for the products that belong to the ‘Electronics’ category and were sold in the month of January. Here’s an example formula:

 

Example

#Start#
sumIf(Sales, Product = 'Electronics' and month(Date) = 1)
#End#

 

This formula calculates the sum of the Sales field only for the rows where the Product field equals ‘Electronics’ and the month of the Date field is January. The and-operator is used to combine the two conditions.

You can also use the sumIf() function with other functions and expressions to calculate more complex calculations. For example, you could calculate the sum of sales values that are greater than the average sales value, using the following formula:

 

Example

#Start#
sumIf(Sales, Sales > avg(Sales))
#End#

 

This formula uses the avg() function to calculate the average sales value, and then uses the sumIf() function to calculate the sum of the Sales field only for the rows where the Sales value is greater than the average sales value.