Iteradores X

Curso de Análisis de Datos con Power BI

Bienvenida

⚠️ Toma la nueva versión del curso

Aprende Power BI

Business Intelligence con Power BI

⚠️ Toma la nueva versión del curso

¿Qué es Power BI?

Arquitectura de Power BI

⚠️ ACTUALIZACIÓN: cómo conectarte a la base de datos

Tipos de conexiones

Preparación de datos

⚠️ Toma la nueva versión del curso

¿Qué es ETL?

Transformar datos con Power Query

Transformaciones

Combinaciones

Modelado de datos

⚠️ Toma la nueva versión del curso

Modelado de datos

Relaciones y filtros

Corrigiendo problemas de modelado

Lenguaje DAX

Usar CALCULATE

Inteligencia de tiempo

Iteradores X

Visualización de datos

Data Storytelling

Crear Visualizaciones

Crear un informe con Power BI

Analítica de datos

Analítica de datos con Power BI

Analítica de datos con Python / R

Entrega de datos

¿Qué es Power BI Service?

Compartir nuestros informes

Crear un dashboard en Power Bi

Implementar una puerta de enlace con Power BI Service

Conclusión

Conclusiones del curso

You don't have access to this class

Keep learning! Join and start boosting your career

If you already have an account,

Resources

Iterative functions are special within DAX. They allow us to create row-level operations and calculate the result. Iterative functions are also known as X functions. Some of these functions are: SUMX, AVERAGEX, MAXX, MINX, STDEVX.S, PERCENTILEX.EXC and CONCATENATEX.

One use case is to multiply two values within the same row and add the results of each multiplication. We do this with the SUMX function.

SUMX Practice

Let's see this in a practical way. From Power BI, connect to the SUMX.xlsx file (it is in the course folder). Load Sheet1.

Using "Specify data", create a measures_table. Now add 2 measures to it, one called Total Sales, which is the sum of Sales Price without VAT. The other measure will be Profit Margin, which corresponds to the sum of the Profit Margin column.

Now generate a table where you can see the total sales and profit margin per location. It would look like this:
Total de ventas de margen de utilidad por sede

Let's suppose we want to obtain the profit per location. Intuition tells us that we should simply multiply the total sales by the profit margin. We would do this with a DAX expression like this:

DAX Profit = Measures_Table[Total Sales]*Measures_Table[Profit Margin].

However, taking this expression to the table does not give the correct result, since we are multiplying the sums of total sales and profit margin. In reality, we must iterate the rows, multiply the sales by the profit margin for each row, and sum the result of that multiplication for each Seat.

To do this, we create a new measure with the following expression:

Profit = SUMX(Sheet1, Table_measure[Total Sales]*Table_measure[Profit Margin]).

The SUMX function receives two parameters. The first is the table to iterate, the second is the expression to sum for each row of the table. At the end we are left with:
Comparación entre ganancia DAX y ganancia por sede

Contribution created with contributions from: Ciro Villafraz and Nestor Mamani.

Contributions 331

Questions 23

Sort by:

Want to see more contributions, questions and answers from the community?

Erik Martinez Santa

If you already have an account,

Curso de Análisis de Datos con Power BI

Iteradores X

SUMX Practice

Apuntes