Cuándo usar columnas calculadas y campos calculados

Al aprender por primera vez cómo usar Power Pivot, la mayoría de los usuarios descubren que la potencia real consiste en agregar o calcular un resultado de alguna manera. Si los datos tienen una columna con valores numéricos, puede agregarla fácilmente seleccionándola en una tabla dinámica o una lista de campos de Power View. Por naturaleza, dado que es numérico, se sumará, promediará, contará o cualquier tipo de agregación que seleccione automáticamente. Esto se conoce como una medida implícita. Las medidas implícitas son excelentes para una agregación rápida y sencilla, pero tienen límites y casi siempre se pueden superar con medidas explícitas y columnas calculadas.

Veamos primero un ejemplo en el que usamos una columna calculada para agregar un nuevo valor de texto para cada fila de una tabla denominada Producto. Cada fila de la tabla Producto contiene todo tipo de información sobre cada producto que vendemos. Tenemos columnas para nombre de producto, color, tamaño, precio de distribuidor, etc. Tenemos otra tabla relacionada denominada Product Category que contiene una columna ProductCategoryName. Lo que queremos es que cada producto de la tabla Producto incluya el nombre de la categoría de producto de la tabla Categoría de producto. En nuestra tabla Producto, podemos crear una columna calculada denominada Categoría de producto como esta:

Nuestra nueva fórmula Product Category usa la función RELATED DAX para obtener los valores de la columna ProductCategoryName en la tabla relacionada Product Category y, a continuación, escribe esos valores para cada producto (cada fila) en la tabla Product.

Este es un gran ejemplo de cómo podemos usar una columna calculada para agregar un valor fijo para cada fila que podemos usar más adelante en el área FILAS, COLUMNAS o FILTROS de la tabla dinámica o en un informe de Power View.

Vamos a crear otro ejemplo en el que queremos calcular un margen de beneficio para nuestras categorías de productos. Este es un escenario común, incluso en muchos tutoriales. Tenemos una tabla Sales en nuestro modelo de datos que tiene datos de transacciones y existe una relación entre la tabla Sales y la tabla Product Category. En la tabla Ventas, tenemos una columna que tiene los importes de ventas y otra columna que tiene costos.

Podemos crear una columna calculada que calcule un importe de beneficios para cada fila restando los valores de la columna COGS de los valores de la columna SalesAmount, así:

Ahora, podemos crear una tabla dinámica y arrastrar el campo Categoría de producto a COLUMNAS, y nuestro nuevo campo Profit al área VALORES (una columna de una tabla en PowerPivot es un campo de la lista de campos de tabla dinámica). El resultado es una medida implícita denominada Sum of Profit. Es una cantidad agregada de valores de la columna de beneficios para cada una de las distintas categorías de productos. Nuestro resultado es similar a este:

En este caso, Profit solo tiene sentido como campo en VALUES. Si colocaremos Profit en el área COLUMNAS, la tabla dinámica tendría el siguiente aspecto:

Nuestro campo Profit no proporciona ninguna información útil cuando se coloca en áreas COLUMNAS, FILAS o FILTROS. Solo tiene sentido como valor agregado en el área VALORES.

Lo que hemos hecho es crear una columna denominada Profit que calcule un margen de beneficio para cada fila de la tabla Sales. A continuación, agregamos Profit al área VALUES de nuestra tabla dinámica, creando automáticamente una medida implícita, donde se calcula un resultado para cada una de las categorías de producto. Si estás pensando que realmente calculamos beneficios para nuestras categorías de productos dos veces, tienes razón. Primero calculamos un beneficio para cada fila de la tabla Ventas y después agregamos Profit al área VALUES donde se agregó para cada una de las categorías de producto. Si también piensa que realmente no es necesario crear la columna calculada Profit, también es correcto. Pero, ¿cómo calculamos nuestros beneficios sin crear una columna calculada Profit?

Beneficio, en realidad se calcularía mejor como una medida explícita.

Por ahora, vamos a dejar nuestra columna calculada Profit en la tabla Sales y Product Category en COLUMNS y Profit en VALUES de nuestra tabla dinámica, para comparar nuestros resultados.

En el área de cálculo de nuestra tabla Sales, vamos a crear una medida denominada Total Profit(para evitar conflictos de nomenclatura). Al final, obtendrá los mismos resultados que antes, pero sin una columna calculada Profit.

En primer lugar, en la tabla Sales, seleccionamos la columna SalesAmount y, a continuación, hacemos clic en Autosuma para crear una medida explícita Sum of SalesAmount. Recuerde, una medida explícita es una que creamos en el área de cálculo de una tabla en Power Pivot. Hacemos lo mismo para la columna COGS. Cambiaremos el nombre de estos Total SalesAmount y Total COGS para que sean más fáciles de identificar.

A continuación, creamos otra medida con esta fórmula:

Total profit:=[ Total SalesAmount] - [Total COGS]

Después de cambiar el formato de nuestra nueva medida total de beneficios a moneda, podemos agregarla a nuestra tabla dinámica.

Puede ver que nuestra nueva medida Beneficio total devuelve los mismos resultados que la creación de una columna calculada Profit y la coloca en VALORES. La diferencia es que nuestra medida total de beneficios es mucho más eficiente y hace nuestro modelo de datos más limpio y más eficiente porque estamos calculando en ese momento y solo para los campos que seleccionamos para nuestra tabla dinámica. No necesitamos realmente esa columna calculada Profit después de todo.

¿Por qué es importante esta última parte? Las columnas calculadas agregan datos al modelo de datos y los datos ocupan memoria. Si actualizamos el modelo de datos, los recursos de procesamiento también son necesarios para volver a calcular todos los valores de la columna Profit. No es necesario ocupar recursos como este porque realmente queremos calcular nuestros beneficios cuando seleccionamos los campos para los que queremos obtener beneficios en la tabla dinámica, como las categorías de productos, la región o las fechas.

Veamos otro ejemplo. Una en la que una columna calculada crea resultados que a primera vista parecen correctos, pero......

En este ejemplo, queremos calcular los importes de ventas como un porcentaje de las ventas totales. Creamos una columna calculada denominada % de ventas en nuestra tabla Ventas, como esta:

Nuestra fórmula indica: Para cada fila de la tabla Sales, divida la cantidad de la columna SalesAmount por el total SUM de todos los importes de la columna SalesAmount.

Si creamos una tabla dinámica y agregamos Categoría de producto a COLUMNAS y seleccionamos nuestra nueva columna % de ventas para ponerla en VALORES, obtenemos una suma total del % de ventas para cada una de nuestras categorías de productos.

De acuerdo. Esto se ve bien hasta el momento. Pero vamos a agregar una segmentación de datos. Agregamos Año calendario y seleccionamos un año. En este caso, seleccionamos 2007. Esto es lo que tenemos.

A primera vista, puede que siga apareciendo correcto. Sin embargo, nuestros porcentajes realmente deberían ser del 100 %, porque queremos conocer el porcentaje de ventas totales para cada una de nuestras categorías de productos para 2007. ¿Qué salió mal?

Nuestra columna % de ventas ha calculado un porcentaje para cada fila que es el valor de la columna SalesAmount dividida por el total de la suma de todos los valores de la columna SalesAmount. Los valores de una columna calculada son fijos. Son un resultado inmutable para cada fila de la tabla. Cuando agregamos % de ventas a nuestra tabla dinámica, se agregó como una suma de todos los valores de la columna SalesAmount. Esa suma de todos los valores de la columna % de ventas siempre será 100%.

Podemos eliminar nuestra columna calculada % de ventas porque no nos va a ayudar. En su lugar, vamos a crear una medida que calcule correctamente nuestro porcentaje de ventas totales, independientemente de los filtros o segmentaciones de datos aplicados.

¿Recuerda la medida TotalSalesAmount que hemos creado anteriormente, la que simplemente suma la columna SalesAmount? Lo usamos como argumento en nuestra medida Beneficios totales y lo vamos a usar de nuevo como argumento en nuestro nuevo campo calculado.

Creamos una nueva medida con la siguiente fórmula:

% de ventas totales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

Esta fórmula indica: Divida el resultado de Total SalesAmount por el total de total de SalesAmount sin filtros de columna o fila distintos a los definidos en la tabla dinámica.

Ahora, si agregamos nuestro nuevo % de ventas totales a la tabla dinámica, obtenemos:

Se ve mejor. Ahora nuestro % de ventas totales para cada categoría de producto se calcula como un porcentaje de las ventas totales para el año 2007. Si seleccionamos un año diferente o más de un año en la segmentación de datos CalendarYear, obtenemos nuevos porcentajes para nuestras categorías de productos, pero nuestro total general sigue siendo del 100 %. También podemos agregar otras segmentaciones de datos y filtros. Nuestra medida % de ventas totales siempre producirá un porcentaje de las ventas totales, independientemente de las segmentaciones de datos o filtros aplicados. Con las medidas, el resultado siempre se calcula según el contexto determinado por los campos de COLUMNAS y FILAS, así como por los filtros o segmentaciones de datos que se apliquen. Este es el poder de las medidas.

Estas son algunas directrices que le ayudarán a decidir si una columna calculada o una medida son adecuadas para una necesidad de cálculo determinada:

Usar columnas calculadas

• Si desea que los nuevos datos aparezcan en FILAS, COLUMNAS o EN FILTROS en una tabla dinámica, o en un EJE, LEYENDA o MOSAICO POR en una visualización de Power View, debe usar una columna calculada. Al igual que las columnas normales de datos, las columnas calculadas se pueden usar como un campo en cualquier área y, si son numéricas, también se pueden agregar en VALORES.

• Si quiere que los nuevos datos sean un valor fijo para la fila. Por ejemplo, tiene una tabla de fechas con una columna de fechas y desea otra columna que contenga solo el número del mes. Puede crear una columna calculada que calcule solo el número de mes a partir de las fechas de la columna Fecha. Por ejemplo, =MES('Fecha'[Fecha]).

• Si desea agregar un valor de texto para cada fila a una tabla, use una columna calculada. Los campos con valores de texto nunca se pueden agregar en VALORES. Por ejemplo, =FORMAT('Fecha'[Fecha],"mmmm") nos proporciona el nombre del mes para cada fecha en la columna Fecha de la tabla Fecha.

Usar medidas

• Si el resultado del cálculo depende siempre de los demás campos que seleccione en una tabla dinámica.

• Si necesita realizar cálculos más complejos, como calcular un recuento basado en un filtro de algún tipo, o calcular un año sobre año o varianza, use un campo calculado.

• Si desea mantener el tamaño del libro al mínimo y maximizar su rendimiento, cree tantos cálculos como sea posible. En muchos casos, todos los cálculos pueden ser medidas, lo que reduce significativamente el tamaño del libro y acelera el tiempo de actualización.

Tenga en cuenta que no hay nada de malo en crear columnas calculadas como lo hicimos con nuestra columna Profit y luego agregarla a una tabla dinámica o un informe. En realidad, es una forma muy buena y fácil de aprender y crear sus propios cálculos. A medida que comprenda estas dos características extremadamente eficaces de Power Pivot, querrá crear el modelo de datos más eficaz y preciso que pueda. Esperemos que lo que ha aprendido aquí le ayude. Hay otros recursos realmente excelentes que pueden ayudarle también. Estos son solo algunos: Contexto en fórmulas de DAX, agregaciones en Power Pivot y centro de recursos de DAX. Y, aunque es un poco más avanzado y está dirigido a profesionales de contabilidad y finanzas, el modelado y análisis de datos de pérdidas y ganancias con el ejemplo de Microsoft Power Pivot en Excel se carga con excelentes ejemplos de modelado de datos y fórmulas.