🀄

1. Aggregation Functions in DAX

Status

Done

Aggregation Functions in DAX

SUM, SUMX, AVERAGE, AVERAGEX, AVERAGEA
MIN, MINX, MAX, MAXX
PRODUCT, PRODUCTX
COUNT, COUNTA, COUNTAX, COUNTROWS, COUNTBLANK, DISTINCTCOUNT
MEDIAN, MEDIANX
STDEV.P, STDEV.S, STDEVX.P, STDEVX.S
VAR.P, VAR.S, VARX.P, VARX.S

Here's the sample dataset used for all the aggregation function practices:

Product	Category	Quantity	UnitPrice	Discount	Region	StockLeft	Rating
A	Phones	10	300	0.10	East	50.0	4
B	Phones	5	250	0.15	East	30.0	3
C	Laptops	2	1000	0.20	West	NaN	5
D	Tablets	8	400	0.05	East	25.0	4
E	Phones	6	320	0.10	West	20.0	3

Aggregation Functions: Python vs DAX

Function	DAX Syntax	Description	DAX Formula Equivalent	Python Expression	Result
`SUM`	`SUM(column)`	Adds up all numbers in a column.	`SUM(Quantity)`	`df['Quantity'].sum()`	31.00
`SUMX`	`SUMX(table, expression)`	Evaluates an expression per row and sums the results.	`SUMX(SalesData, Quantity * UnitPrice)`	`(df['Quantity'] * df['UnitPrice']).sum()`	11370.00
`AVERAGE`	`AVERAGE(column)`	Returns the arithmetic mean of a column.	`AVERAGE(UnitPrice)`	`df['UnitPrice'].mean()`	454.00
`AVERAGEX`	`AVERAGEX(table, expression)`	Evaluates an expression per row and averages the results.	`AVERAGEX(SalesData, Quantity * (1 - Discount))`	`(df['Quantity'] * (1 - df['Discount'])).mean()`	5.57
`AVERAGEA`	`AVERAGEA(column)`	Includes text and logical values in average calculation.	`AVERAGEA({TRUE, FALSE, 5, "3"})`	`pd.Series([True, False, 5, '3']).apply(pd.to_numeric, errors='coerce').mean()`	2.25
`MIN`	`MIN(column)`	Returns the smallest numeric value in a column.	`MIN(UnitPrice)`	`df['UnitPrice'].min()`	250.00
`MINX`	`MINX(table, expression)`	Returns the smallest value after evaluating an expression per row.	`MINX(SalesData, Quantity * UnitPrice)`	`(df['Quantity'] * df['UnitPrice']).min()`	1250.00
`MAX`	`MAX(column)`	Returns the largest numeric value in a column.	`MAX(UnitPrice)`	`df['UnitPrice'].max()`	1000.00
`MAXX`	`MAXX(table, expression)`	Returns the largest value after evaluating an expression per row.	`MAXX(SalesData, Quantity * UnitPrice)`	`(df['Quantity'] * df['UnitPrice']).max()`	3200.00
`PRODUCT`	`PRODUCT(column)`	Returns the product of all values in a column.	`PRODUCT(Quantity)`	`df['Quantity'].prod()`	4800
`PRODUCTX`	`PRODUCTX(table, expression)`	Returns the product of evaluated expressions per row.	`PRODUCTX(SalesData, Quantity * UnitPrice)`	`(df['Quantity'] * df['UnitPrice']).prod()`	7.36e+13
`COUNT`	`COUNT(column)`	Counts non-blank values in a column.	`COUNT(StockLeft)`	`df['StockLeft'].count()`	4
`COUNTA`	`COUNTA(column)`	Counts non-empty values (includes text/logical).	`COUNTA(Category)`	`df['Category'].count()`	5
`COUNTAX`	`COUNTAX(table, expression)`	Counts non-blank results from an expression.	`COUNTAX(SalesData, Quantity * (1 - Discount))`	`(df['Quantity'] * (1 - df['Discount'])).count()`	5
`COUNTROWS`	`COUNTROWS(table)`	Returns the number of rows in a table.	`COUNTROWS(SalesData)`	`len(df)`	5
`COUNTBLANK`	`COUNTBLANK(column)`	Counts the number of blank values in a column.	`COUNTBLANK(StockLeft)`	`df['StockLeft'].isna().sum()`	1
`DISTINCTCOUNT`	`DISTINCTCOUNT(column)`	Returns the number of unique values in a column.	`DISTINCTCOUNT(Category)`	`df['Category'].nunique()`	3
`MEDIAN`	`MEDIAN(column)`	Returns the middle value in a column.	`MEDIAN(UnitPrice)`	`df['UnitPrice'].median()`	320.00
`MEDIANX`	`MEDIANX(table, expression)`	Evaluates and returns the median of expression results.	`MEDIANX(SalesData, Quantity * UnitPrice)`	`(df['Quantity'] * df['UnitPrice']).median()`	2000.00
`STDEV.P`	`STDEV.P(column)`	Standard deviation for entire population.	`STDEV.P(Rating)`	`df['Rating'].std(ddof=0)`	0.748
`STDEV.S`	`STDEV.S(column)`	Sample standard deviation.	`STDEV.S(Rating)`	`df['Rating'].std(ddof=1)`	0.836
`STDEVX.P`	`STDEVX.P(table, expression)`	Standard deviation for population from evaluated expression.	`STDEVX.P(SalesData, Quantity * UnitPrice)`	`(df['Quantity'] * df['UnitPrice']).std(ddof=0)`	703.58
`STDEVX.S`	`STDEVX.S(table, expression)`	Standard deviation for sample from evaluated expression.	`STDEVX.S(SalesData, Quantity * UnitPrice)`	`(df['Quantity'] * df['UnitPrice']).std(ddof=1)`	786.00
`VAR.P`	`VAR.P(column)`	Population variance of a column.	`VAR.P(Rating)`	`df['Rating'].var(ddof=0)`	0.56
`VAR.S`	`VAR.S(column)`	Sample variance of a column.	`VAR.S(Rating)`	`df['Rating'].var(ddof=1)`	0.70
`VARX.P`	`VARX.P(table, expression)`	Population variance of evaluated expression results.	`VARX.P(SalesData, Quantity * UnitPrice)`	`(df['Quantity'] * df['UnitPrice']).var(ddof=0)`	494,991.6
`VARX.S`	`VARX.S(table, expression)`	Sample variance of evaluated expression results.	`VARX.S(SalesData, Quantity * UnitPrice)`	`(df['Quantity'] * df['UnitPrice']).var(ddof=1)`