Aggregation Functions () | Power BI || Belayet Hossain

An aggregate function in Power BI is a type of function that performs a calculation on a set of values and returns a single value.

These functions are commonly used to summarize data, allowing you to compute totals, averages, counts, and other statistics.

Aggregate functions are essential in data analysis and reporting because they help simplify large datasets into meaningful insights.

??1. SUM

• Purpose: Returns the sum of numeric values in a column.
• Use: To calculate the total sum of a numerical column.
• Syntax: ??????(????????????)
• Example:

Scenario: Total sales for all products.

DAX = ??????('?????????????? ??????????'[??????????])

• Result: 450
• Explanation: Adds up all sales values to give a total of 450.

??2. SUMX

• Purpose: Returns the sum of an expression evaluated over a table.
• Use: Use when you need to sum results from an expression.
• Syntax: ????????(??????????, ????????????????????)
• Example:

Scenario: Total revenue from sales for all products.

DAX: ?????????????????????????????? = ????????(??????????, ??????????[??????????????????????] * ??????????[??????????])

• Result: 7250
• Explanation: Multiplies sales by units sold for each product and sums the result.

100*10 + 200*20 + 150*15 = 1000+4000+2250=7250

??3. COUNT

• Purpose: Counts the number of rows in the specified column that contain non-blank values.
• Use: To count numeric values or non-blank cells in a column.
• Syntax: ??????????(????????????)
• Example:

Scenario: Counting the number of sales transactions with recorded amounts.

DAX: CountSales = COUNT(Sales[SalesAmount])

• Result : 2
• Explanation: The COUNT function counts non-blank values in a column.

??4. COUNTA

• Purpose: Counts the number of rows in the specified column that contain non-blank values.
• Use: To count all types of values, including text and numbers.
• Syntax: COUNTA(<column>)
• Example:

Scenario: Use to count products with a value in the sales column, ignoring blanks.

DAX: CountNonBlank = COUNTA(Sales[SalesAmount])

• Result: 2 (Products A and B)
• Explanation: Similar to COUNT but works with non-numeric data types too.

??5. COUNTAX

• Purpose: Counts the number of rows that result from evaluating an expression.
• Use: To count rows based on a custom expression.
• Syntax: ??????????????(<??????????>, <????????????????????>)
• Example:

Scenario: Counting the number of products with sales amounts greater than 100.

DAX: CountSalesAbove100 = COUNTAX(Sales, Sales[SalesAmount] > 100)

• Result: 2 (Products B and C)
• Explanation: The COUNTAX function counts rows where the expression evaluates to true.

??6. COUNTX

• Purpose: Counts the number of rows that result from evaluating an expression.
• Use: Similar to COUNTAX, but counts rows based on the result of an expression.
• Syntax: ????????????(<??????????>, <????????????????????>)
• Example:

Scenario: Counting the number of products with more than 15 units.

DAX: CountUnitsAbove15 = COUNTX(Sales, Sales[Units] > 15)

• Result : 2 (Products B and C)
• Explanation: The COUNTX function counts rows where the expression evaluates to true.

??7. COUNTROWS

• Purpose: Counts the number of rows in a table or table expression.
• Use: To count the number of rows in a table or result of a query.
• Syntax: ??????????????????(<??????????>)
• Example:

Scenario: Counting the total number of sales records.

DAX: RowCount = COUNTROWS(Sales)

• Result : 3
• Explanation: The COUNTROWS function counts the total number of rows in a table.

??8. AVERAGE

• Purpose: Calculates the average (arithmetic mean) of a numeric column.
• Use: To find the average value of a numerical field.
• Syntax: ??????????????(<????????????>)
• Example:

Scenario: Finding the average sales amount per product.

DAX: AverageSales = AVERAGE(Sales[SalesAmount])

• Result: 200
• Explanation: The AVERAGE function calculates the mean of values in the specified column.

??9. AVERAGEA

• Purpose: The AVERAGEA function in Power BI returns the arithmetic mean (average) of a set of numbers, but it also considers logical and text values. Specifically:

1. TRUE is counted as 1
2. FALSE or non-numeric text values are counted as 0

• Use: This function is useful when you have a column that contains logical or text values, and you want to include them in the average calculation.
• Syntax: ????????????????(<????????????>)
• Example:

Scenario: You want to calculate the average of the Value column, considering the text and logical values using the AVERAGEA function.

DAX: AverageA_Value = AVERAGEA(TableA[Value])

• Result : 6
• Explanation:

>> 10, 5, and 20 are numbers and are included in the average calculation.

>> TRUE is treated as 1.

>> FALSE and the text value "Text" are treated as 0.

So the calculation would be: Average= 10+1+5+0+0+20=36 /6 = 6

??10. AVERAGEX

• Purpose: Calculates the average of an expression evaluated for each row in a table.
• Use: When you need to perform calculations or aggregations before averaging.
• Syntax: ????????????????(<??????????>, <????????????????????>)
• Example:

Scenario: We want to calculate the average revenue per product. Revenue is calculated as Units Sold * Unit Price for each row.

DAX: AverageRevenuePerProduct = AVERAGEX ( SalesTable, SalesTable[Units Sold] * SalesTable[Unit Price] )

• Result : the result of AVERAGEX is 332.5.
• Explanation: The AVERAGEX function computes the average of these calculated values:

(10*15 + 20*12 + 30*18 + 40*10 = 150 + 240 + 540 + 400 = 1330 / 4 = 332.5

??11. DISTINCTCOUNT

• Purpose: Counts the number of distinct (unique) values in a column.
• Use: To find the number of unique entries.
• Syntax: ??????????????????????????(<????????????>)
• Example:

Scenario: Finding the number of distinct sales amounts.

DAX: UniqueProducts = DISTINCTCOUNT(Sales[SalesAmount])

• Result: 2
• Explanation: 100 & 300 are unique value in this sales table.

??12. DISTINCTCOUNTNOBLANK

• Purpose: Counts the number of distinct values in a column, ignoring blank values.
• Use: To find unique non-blank entries.
• Syntax: ????????????????????????????????????????(<????????????>)

• Example:

Scenario: Finding the number of unique non-blank sales amounts.

DAX: UniqueNonBlankProducts = DISTINCTCOUNTNOBLANK(Sales[SalesAmount])

• Result : 1
• Explanation: only 100 is the non-blank unique value in this sales table.

??13. MAX

• Purpose: Returns the maximum value in a column.
• Use: To find the highest value in a numerical field.
• Syntax: ??????(<????????????>)
• Example:

Scenario: Finding the highest sales amount.

DAX: MaxSales = MAX(Sales[SalesAmount])

• Result : 300
• Explanation: 300 is the highest sales amount in this sales table

??14. MAXA

• Purpose: It returns the maximum value from a column, considering both numbers and text. It evaluates both numeric and non-numeric data, where text is treated as 0, and logical values (TRUE and FALSE) are evaluated as 1 and 0, respectively.
• Use:

Numeric values: It returns the highest number.

Text values: It returns 0 if text is present in the column.

Logical values: It treats TRUE as 1 and FALSE as 0.

• Syntax: MAXA(<column>)
• Example:

Scenario: To find the maximum value in the SalesAmount column, considering both numeric and non-numeric data:

DAX: MAXA(SalesData[SalesAmount])

• Result : 250
• Explanation: The maximum numeric value is 250, so that is returned.

??15. MAXX

• Purpose: The MAXX function returns the maximum value in a column or expression, evaluated for each row of a table.
• Use: It is useful when you need to find the maximum of a calculated expression across rows, rather than a simple column.
• Syntax: MAXX(<Table>, <Expression>)
• Example:

Scenario: You want to find the maximum revenue (UnitsSold * UnitPrice) from the table.

DAX: MAXX('Sales', 'Sales'[UnitsSold] * 'Sales'[UnitPrice])

Table: Sales

• Result : 200
• Explanation:

10*200 = 200, 5*15= 75 , 8*25 = 200

The maximum value is 200, so MAXX returns 200.

??16. MIN

• Purpose: Returns the minimum value in a column.
• Use: To find the lowest value in a numerical field.
• Syntax: ??????(<????????????>)
• Example:

Scenario: Finding the lowest sales amount.

DAX: MinSales = MIN(Sales[SalesAmount])

• Result : 100
• Explanation: The minimum value is 100, so MIN returns 100.

??17. MINA

• Purpose: The MINA() function in Power BI returns the smallest value from a column, considering all data types, including text, numbers, and Boolean values.
• Use: It is similar to the MIN() function but treats text and Boolean values differently:

Text values are considered smaller than numbers.

Boolean values are treated as TRUE = 1 and FALSE = 0.

• Syntax: MINA(<Column>)
• Example:

Scenario:

DAX: SmallestValue = MINA(TableA[Value])

Table: TableA

• Result: The smallest value from the column Value is determined as:

FALSE is treated as 0, which is the smallest.

Text is considered smaller than numbers, so it would be the smallest if present, depending on the dataset.

For this specific data set, "Text" would be the smallest value.

??18. MINX

• Purpose: The MINX function returns the smallest value from an expression evaluated row by row in a table.
• Use: You use MINX when you want to evaluate a specific expression (usually a calculation) across rows in a table and return the minimum result.
• Syntax: MINX(<table>, <expression>)
• Example:

Scenario: We want to compute the smallest total sales value across the products.

DAX: MINX(SalesTable, SalesTable[Quantity] * SalesTable[Price])

• Result : 150
• Explanation: 10*50 = 500, 5*30=150, 8*20=160, 6*60=360

The smallest total sales value is 150, so the result of the MINX function will be 150.

??19. PRODUCT

• Purpose: Multiplies all the values in a column.
• Use: To calculate the product of all values in a numeric column.
• Syntax: ??????????????(<????????????>)
• Example:

Scenario: Multiply all sales together.

DAX: PRODUCT('Product Sales'[Sales])

• Result : 100
• Explanation: 10*20*5 = 1000

??Key Difference:

#powerbi #aggregate #fucntion #dax #measure