Creating basic table measures with DAX functions

DAX plays a critical role in extending the analytical capabilities of Power BI, particularly with the help of table measures. DAX is not just limited to Power BI, it is also used in other Microsoft tools like Excel (in Power Pivot) and SQL Server Analysis Services (SSAS). Table measures with DAX functions helps to summarize data, perform calculations across columns and tables and leverage the full potential of Power BI for data modeling, analysis, and reporting.

Furthermore, table measures with DAX also help to perform calculations at the row level, which is essential for creating dynamic and context-sensitive measures. These functions simplify the process of making sense of large datasets by providing concise and relevant summaries that can be easily visualized and interpreted. The most common aggregation functions include SUM, AVERAGE, MAX, MIN, DIVIDE, PRODUCT, and COUNT.

Table measures with SUM & SUMX

SUM and SUMX are both DAX functions used to perform summation, but they work differently and are used in different scenarios. The SUM function is used to add up all the values in a single column. This function typically calculates overall metrics such as total sales, revenue, or quantity sold.

Equation = SUM (Table[column])

Result = Row1 + Row2 + Row3 + ......n

Similarly, the SUMX function is used to perform row-by-row calculations and then sum the results. This is particularly useful for evaluating an expression for each row and then summing the results of that expression.

Equation = SUMX (Table, Table[Column1] - Table[Column2]
//The arithmatic operator can be +, -, *, / etc

Result = {Column1 Row1 - Column2 Row1} + {Column1 Row2 - Column2 Row2} + {Column1 Row3 - Column2 Row3} + .......{Column1 Row(n) - Column2 Row(n)}

Measures with AVERAGE, AVERAGEA & AVERAGEX

The AVERAGE function calculates the arithmetic mean of the value of all the rows in a column.

Equation = AVERAGE (Table[Column])

Result = {Row1 + Row2 + Row3 + ......n} ÷ n 

The AVERAGEA function is similar to AVERAGE but includes logical values and text in its calculations. This can be useful when working with datasets that contain a mix of numbers, text, and logical values.

• The TRUE values are given values as 1
• Empty text or normal text is given value as 0
• FALSE values are given value as 0

Equation = AVERAGEA (Table[Column])

Result = Row1 + Row2 + Row3 + ......n

The AVERAGEX, is an iterator function that calculates the average of an expression evaluated for each row in a table. This allows for more granular control over what is averaged, similar to SUMX.

Using the sample dataset create a new measure to calculate the average sales and average discount percent.

Average sale = AVERAGE (transactions[Revenue])

Average discount percent = AVERAGEX(transactions,(transactions[Discount]/transactions[Price])*100)

The Average sale is visualized in the card and the Average earning is visualized in gauge chart in the report view of Power BI.

MAX table measure

The MAX function returns the maximum value of the column.

Equation = MAX (Table[Column])

Max discount = MAX (transactions[Discount])

Result: 250

The MAXA function includes text and logical values in its calculations, treating non-numeric as 0 and TRUE as 1.

Equation = MAXA (Table[Column])

The MAXX function evaluates an expression for each row and returns the maximum value.

Equation = MAXX (Table, Expression)

Max discount percent = MAXX (transactions,(transactions[Discount]/transactions[Price])*100)

Result: 20%

MIN table measure

The MIN function returns the smallest value in a column.

Equation = MAX (Table[Column])

Min units sold = MIN (transactions[Quantity])

Result: 4

The MINA function includes text and logical values in its calculations, treating non-numeric as 0 and TRUE as 1.

Equation = MINA (Table[Column])

The MINX function evaluates an expression for each row and returns the maximum value.

Equation = MINX (Table, Expression)

Min discount percent = MINX (transactions,(transactions[Discount]/transactions[Price])*100)

Result: 0%

COUNT, COUNTA, COUNTX, COUNTBLANK, COUNTROWS & DISTINCTCOUNT

The COUNT function counts the number of rows or records in a specified column. It determines the frequency or occurrence of all records including empty, null, and non-empty records in a column.

Equation = COUNT (Table[Column])

No of products = COUNT (transactions[Product])

Result: 20

COUNTA function counts the number of non-blank only in a column. This includes both numeric and text values.

Equation = COUNTA (Table[Column])

The COUNTX function counts the number of rows in a table where the expression evaluates to a non-blank value. This is useful for counting records with conditions or calculations.

Equation = COUNTX (Table[Column])

No of discounted products = COUNTX (transactions,if(transactions[Discount] > 0,1,BLANK()))

Result: 18

The COUNTBLANK function counts the number of blank or empty cells in a specified column. It’s useful for identifying missing data or gaps in datasets.

Equation = COUNTA (Table[Column])

The COUNTROWS function returns the number of rows in a table, which is useful for getting a quick count of all records in a dataset.

Equation = COUNTROWS (Table[Column])

The DISTINCTCOUNT function counts the number of rows with unique values in a column.

Equation = DISTINCTCOUNT (Table[Column])

No of productcats = DISTINCTCOUNT (transactions[Category])

Result: 3

PRODUCT table measure

The PRODUCT function multiplies all numeric values in a specified column. It’s particularly useful in scenarios involving compounded growth rates or multiplicative effects.

Equation = PRODUCT (Table[Column])

The PRODUCTX function is an iterator that evaluates an expression for each row and then returns the product of those values.

Equation = PRODUCTX (Table[Column])

DIVIDE table measure

Similar to PRODUCT, the DIVIDE function in Power BI handles division operations, with a focus on avoiding errors caused by division by zero. It provides a third argument that specifies an alternate result in case the denominator is zero.

DIVIDE (Numerator, Denominator)

Salesdivide = DIVIDE (SUM(transactions[Quantity]), DISTINCTCOUNT(transactions[Category]))

Result: 180

Table measures with aggregation functions are fundamental tools for data summarization and analysis. These functions help to create robust and meaningful data models that can drive informed decision-making across different domains. Understanding when and how to use each aggregation function is the key to effectively analyzing and interpreting data within Power BI.