¿Cuáles son las principales funciones de agregación de DAX?

How to generate basic measures in DAX for business metrics?

When leading a data analysis, one of the first questions that arises is to quantify the transactions within the data model. To address this task, we employ fundamental metrics with DAX, a powerful data expression language in Power BI. Learning how to handle these functions will allow you to quickly answer key operational questions and optimize the tracking of business metrics.

How do you count the number of transactions?

Let's start with the number of transactions, a basic but essential indicator. To calculate it, you build a measure that counts the rows of the sales table.

Number of transactions = COUNTROWS('Sales')

It is important to adjust the display format. By removing the units display and showing the exact number, you can see that there are, for example, 14,202 transactions, instead of a rounded 14,000.

How to calculate the number of unique customers?

Another key metric is the number of customers. Although we can use a basic counting function, it is crucial to apply business logic that allows us to get a unique customer count.

Number of customers = DISTINCTCOUNT('Customers'[Customer ID])

Counting a dimension, such as customer IDs from the customer table, ensures that you get the correct number of distinct customers, for example, 1,179. However, if you count the 'Customer' column in the sales table, you could get an erroneously high number due to multiple purchases by a single customer.

How to generate a visualization with aggregated data?

To explore sales by year and summary measures, it is essential to create visual tables that show key data such as total sum of sales and average sales.

Average Sales = AVERAGE('Sales'[Sales Price Excluding VAT])

At this point, each sales measure is made up of different DAX functions that allow:

• SUM(): Sum the total of the sales.
• AVERAGE(): Calculate the average sales.
• MAX() and MIN(): Identify the maximum and minimum sales values for each year.

What other DAX mathematical functions can we use?

DAX offers a wide repertoire for data analysis. Among the most commonly used mathematical functions are:

• SUM() and SUBTRACT(): For adding and subtracting quantities, useful in calculations such as gross margin.
• MULTIPLICATION and DIVISION(): For proportional and rate calculations.
• DIVIDE(): Allows to handle divisions with an alternative value in case of error due to division by zero.

These functions are essential to calculate statistics such as minimum and maximum values or to perform mathematical base operations on business indicators. Using DAX properly turns data aggregations into an efficient task, enhancing the tracking and analysis of crucial metrics.

Where to find more DAX resources?

Learning DAX is a must for any analyst looking to hone their skills in Power BI or any other platform that uses this language. Microsoft's official documentation is an excellent resource to delve deeper into the use and applications of DAX, including advanced examples and best practices - keep exploring and don't stop learning!