Aggregate function

SUM

Utilized for obtaining the aggregate value of a measure, such as the total number.

For instance, the total sales can be determined by summing up the individual sales values.

Syntax – SUM(expr)

Example: We are calculating the sum of sales.

Example Queries:

• SUM([Retail].[sales])
• SUM([Retail].[profit])
• SUM([Retail].[shipping_cost])
• SUM([Retail].[Order Discount])
• SUM([Retail].[order_discount])

AVG

The AVG function is employed to calculate the average for the selected measures.

For instance, to determine the average sales, users can utilize AVG (“Orders.Sales”).

Syntax – AVG(expr)

Example: We are calculating the average of sales.

Example Queries:

• AVG([orders].[sales])
• AVG([orders].[profit])
• AVG([orders].[shipping_cost])
• AVG([product].[total_discount])
• AVG([orders].[order_discount])

MIN

The MIN function is utilized to retrieve the minimum value of the selected measure.

For instance, to obtain the minimum sales value, one can use the MIN (“Orders.Sales”) function.

Syntax – MIN(expr)

Example: We are selecting minimum sales value.

Example Queries:

• MIN([Retail].[Sales])
• MIN([Retail].[Profit])
• MIN([Retail].[shipping_cost])
• MIN([Retail].[total_discount])
• MIN([Retail].[order_discount])

MAX

The MAX function is employed to obtain the maximum value of the selected measure.

For instance, to retrieve the maximum sales value, you can use the MAX (“Orders.Sales”) function.

Syntax – Max(expr)

Example: We are selecting maximum sales value.

Example Queries:

• MAX([Retail].[sales])
• MAX([Retail].[profit])
• MAX([Retail].[shipping_cost])
• MAX([Retail].[total_discount])
• MAX([Retail].[order_discount])

COUNT

The COUNT function is applied to obtain the total count of the selected measure.

For instance, to determine the total number of items sold (sales), you can use the COUNT (“Orders.Sales”) function.

Syntax – COUNT(expr)

Example: We are finding a count of sales.

Example Queries:

• COUNT([Retail].[Order_id])
• COUNT([Retail].[Customer_id])
• COUNT([Retail].[Product_id])
• COUNT([Retail].[Address_id])
• COUNT([Retail].[Order_date])

COUNT DISTINCT

The COUNT DISTINCT function is applied to identify duplicated or overlapping data.

For instance, if there are repeated instances of a customer’s name in the data, you can use the COUNT (DISTINCT “Orders.Customer Name”) function.

Syntax – COUNT(DISTINCT (expr))

Example: We are finding a distinct count of sales.

Example Queries:

• COUNT(DISTINCT([Retail].[Customer_id]))
• COUNT(DISTINCT([Retail].[Order_id]))
• COUNT(DISTINCT([Retail].[Product_id]))
• COUNT(DISTINCT([Retail].[City]))
• COUNT(DISTINCT([Retail].[Order_date]))

STDDEV

To calculate the standard deviation of a set of values in a dataset. Standard deviation is a measure of the amount of variation or dispersion in a set of values. It indicates how much individual values in a dataset deviate from the mean (average) of the dataset.

Syntax – STDDEV(expr)

Example: We are calculating the standard deviation of sales.