Aggregation functions in Power BI allow you to perform calculations on your data to generate valuable insights. This guide explores these functions, breaking them down into sections for ease of understanding, complete with syntax and examples.
1. Numeric Aggregation Functions
1.1 SUM
Calculates the total of a numeric column.
Syntax:
SUM(<column>)Example: To calculate total sales from the Sales[Amount] column:
Total Sales = SUM(Sales[Amount])1.2 SUMX
Performs row-by-row calculations and sums the results.
Syntax:
SUMX(<table>, <expression>)Example: Calculate total revenue by multiplying Quantity and Price for each row:
Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[Price])1.3 AVERAGE
Calculates the average of a numeric column.
Syntax:
AVERAGE(<column>)Example: Find the average sales amount:
Average Sales = AVERAGE(Sales[Amount])1.4 AVERAGEX
Calculates the average of an expression evaluated row by row.
Syntax:
AVERAGEX(<table>, <expression>)Example: Find the average revenue per transaction:
Average Revenue = AVERAGEX(Sales, Sales[Quantity] * Sales[Price])1.5 MIN
Finds the smallest value in a numeric column.
Syntax:
MIN(<column>)Example: Identify the smallest sale amount:
Smallest Sale = MIN(Sales[Amount])1.6 MAX
Finds the largest value in a numeric column.
Syntax:
MAX(<column>)Example: Identify the largest sale amount:
Largest Sale = MAX(Sales[Amount])1.7 MINX
Evaluates an expression for each row and returns the smallest value.
Syntax:
MINX(<table>, <expression>)Example: Find the smallest revenue per row:
Smallest Revenue = MINX(Sales, Sales[Quantity] * Sales[Price])1.8 MAXX
Evaluates an expression for each row and returns the largest value.
Syntax:
MAXX(<table>, <expression>)Example: Find the largest revenue per row:
Largest Revenue = MAXX(Sales, Sales[Quantity] * Sales[Price])1.9 COUNT
Counts the number of non-blank rows in a column.
Syntax:
COUNT(<column>)Example: Count the number of sales transactions:
Transaction Count = COUNT(Sales[TransactionID])1.10 COUNTA
Counts all non-blank values in a column.
Syntax:
COUNTA(<column>)Example: Count the number of entries in the Sales[Region] column:
Region Count = COUNTA(Sales[Region])1.11 COUNTX
Counts rows that evaluate to non-blank in an expression.
Syntax:
COUNTX(<table>, <expression>)Example: Count rows where Quantity multiplied by Price is non-blank:
Non-Blank Revenue Count = COUNTX(Sales, Sales[Quantity] * Sales[Price])1.12 DISTINCTCOUNT
Counts the distinct values in a column.
Syntax:
DISTINCTCOUNT(<column>)Example: Count the distinct regions in the Sales[Region] column:
Distinct Regions = DISTINCTCOUNT(Sales[Region])2. Statistical Aggregations
2.1 STDEV.P
Calculates the standard deviation for the entire population.
Syntax:
STDEV.P(<column>)Example: Find the standard deviation of sales amounts:
Sales Std Dev = STDEV.P(Sales[Amount])2.2 STDEV.S
Calculates the standard deviation for a sample.
Syntax:
STDEV.S(<column>)Example: Find the sample standard deviation of sales
amounts:
Sample Sales Std Dev = STDEV.S(Sales[Amount])2.3 VAR.P
Calculates the variance for the entire population.
Syntax:
VAR.P(<column>)Example: Calculate the variance of sales amounts:
Sales Variance = VAR.P(Sales[Amount])2.4 VAR.S
Calculates the variance for a sample.
Syntax:
VAR.S(<column>)Example: Calculate the sample variance of sales amounts:
Sample Sales Variance = VAR.S(Sales[Amount])3. Other Aggregation Functions
3.1 FIRSTNONBLANK
Returns the first non-blank value in a column.
Syntax:
FIRSTNONBLANK(<column>, <expression>)Example: Find the first non-blank region:
First Region = FIRSTNONBLANK(Sales[Region], 1)3.2 LASTNONBLANK
Returns the last non-blank value in a column.
Syntax:
LASTNONBLANK(<column>, <expression>)Example: Find the last non-blank region:
Last Region = LASTNONBLANK(Sales[Region], 1)3.3 MEDIAN
Returns the median of a column.
Syntax:
MEDIAN(<column>)Example: Find the median sales amount:
Median Sales = MEDIAN(Sales[Amount])3.4 MEDIANX
Returns the median of an expression evaluated for each row.
Syntax:
MEDIANX(<table>, <expression>)Example: Find the median revenue:
Median Revenue = MEDIANX(Sales, Sales[Quantity] * Sales[Price])3.5 PERCENTILE.INC
Returns a value corresponding to the specified percentile (inclusive
method).
Syntax:
PERCENTILE.INC(<column>, <percentile>)Example: Find the 90th percentile of sales:
90th Percentile Sales = PERCENTILE.INC(Sales[Amount], 0.9)3.6 PERCENTILE.EXC
Returns a value corresponding to the specified percentile (exclusive
method).
Syntax:
PERCENTILE.EXC(<column>, <percentile>)Example: Find the 90th percentile of sales using the
exclusive method:
90th Percentile Sales (Excl) = PERCENTILE.EXC(Sales[Amount], 0.9)This blog provides a comprehensive understanding of Power BI’s aggregation
functions. By mastering these, you can unlock the full potential of data modeling
and analysis in Power BI.
No comments:
Post a Comment