# 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.

## MAX table measure

The MAX function returns the maximum value of the column.

`Equation = MAX (Table[Column])`

In the sample dataset find out the maximum discount by using the equation

`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)`

In the sample dataset find the transaction that got the maximum discount percent using the equation

`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])`

In the sample dataset find out the minimum discount by using the equation

`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)`

In the sample dataset find the transaction that got the minimum discount percent using the equation

`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])`

In the sample dataset find out the the number of products.

`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])`

In the sample dataset count the number of products that are discounted.

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

Result: 18

Here the conditional expression ‘if(transactions[Discount] > 0) will return text value ‘1’ when is true else will return a blank value.

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])`

In the sample dataset count the number of product categories listed.

`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)`

To understand product category performance per unit sold by computing the average number of units sold per category.

`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.

I work as an editor and writer for Project Guru. I have a keen interest in new and upcoming learning and teaching methods. I have worked on numerous scholarly projects in the fields of management, marketing and humanities in the last 10 years. Currently, I am working in the footsteps of the National Education Policy of India to help and support fellow professors to emphasise interdisciplinary research and curriculum design.

I am a Senior Analyst at Project Guru, a research and analytics firm based in Gurugram since 2012. I hold a master’s degree in economics from Amity University (2019). Over 4 years, I have worked on worked on various research projects using a range of research tools like SPSS, STATA, VOSViewer, Python, EVIEWS, and NVIVO. My core strength lies in data analysis related to Economics, Accounting, and Financial Management fields.

## Discuss