Status
Done
Aggregation Functions in DAX
SUM,SUMX,AVERAGE,AVERAGEX,AVERAGEAMIN,MINX,MAX,MAXXPRODUCT,PRODUCTXCOUNT,COUNTA,COUNTAX,COUNTROWS,COUNTBLANK,DISTINCTCOUNTMEDIAN,MEDIANXSTDEV.P,STDEV.S,STDEVX.P,STDEVX.SVAR.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) |