Cuándo usar columnas calculadas y campos calculados

Cuando se aprende a usar Power Pivot por primera vez, la mayoría de los usuarios descubre que la potencia real está en agregar o calcular un resultado de alguna manera. Si los datos tienen una columna con valores numéricos, puede agregarla fácilmente seleccionándose en una tabla dinámica o Power View lista de campos. Por naturaleza, como es numérico, se sumará automáticamente, se promediará, contará o cualquier tipo de agregación que seleccione. Esto se conoce como una medida implícita. Las medidas implícitas son excelentes para la agregación rápida y fácil, pero tienen límites y esos límites casi siempre se pueden superar con medidas explícitas ycolumnas 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 del producto, Color, Tamaño, Precio del distribuidor, etc. Tenemos otra tabla relacionada denominada Categoría de producto 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 categoría de producto usa la función RELATED DAX para obtener valores de la columna ProductCategoryName en la tabla Categoría de producto relacionada y, a continuación, escribe esos valores para cada producto (cada fila) en la tabla Producto.

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 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 Ventas en nuestro modelo de datos que tiene datos de transacciones y hay una relación entre la tabla Ventas y la tabla Categoría de producto. En la tabla Ventas, tenemos una columna que tiene importes de ventas y otra columna que tiene costos.

Podemos crear una columna calculada que calcule una cantidad de beneficios para cada fila restando valores de la columna COGS de los valores de la columna SalesAmount, como este:

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

En este caso, El beneficio solo tiene sentido como un campo en VALORES. Si colocamos Profit en el área COLUMNAS, nuestra tabla dinámica tendría este aspecto:

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

Lo que hemos hecho es crear una columna denominada Beneficio que calcule un margen de beneficio para cada fila de la tabla Ventas. A continuación, agregamos Beneficios al área VALORES 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 piensa que hemos calculado el beneficio de nuestras categorías de productos dos veces, tiene razón. Primero calculamos un beneficio para cada fila de la tabla Ventas y, a continuación, agregamos Beneficios al área VALORES donde se agregó para cada una de las categorías de producto. Si también piensa que no es necesario crear realmente la columna Calculado de beneficios, también tiene razón. Pero, ¿cómo calculamos nuestros beneficios sin crear una columna calculada con beneficios?

Los beneficios, realmente se calcularían mejor como una medida explícita.

Por ahora, vamos a dejar nuestra columna Cálculo de beneficios en la tabla Ventas y categoría de producto en COLUMNAS y Beneficio en VALORES de nuestra tabla dinámica, para comparar nuestros resultados.

En el área de cálculo de nuestra tabla Ventas, vamos a crear una medida denominada Beneficio total(para evitar conflictos de nomenclatura). Al final, dará los mismos resultados que lo que hicimos antes, pero sin una columna calculada con beneficios.

En primer lugar, en la tabla Ventas, seleccionamos la columna SalesAmount y, a continuación, hacemos clic en Autosum para crear una medida explícita suma de VentasAmount. Recuerde que una medida explícita es la 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 TOTALES de ventasAmount y Totales de 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 DE VENTAS]

Después de cambiar el formato de la nueva medida beneficio total a moneda, podemos agregarlo 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 con beneficios y, a continuación, la coloca en VALORES. La diferencia es que nuestra medida beneficio total es mucho más eficiente y hace que nuestro modelo de datos sea más limpio y más delgado porque estamos calculando en ese momento y solo para los campos que seleccionamos para nuestra tabla dinámica. No necesitamos realmente esa columna calculada de beneficios después de todo.

¿Por qué es importante esta última parte? Las columnas calculadas agregan datos al modelo de datos y los datos recuperan memoria. Si actualizamos el modelo de datos, también se necesitan recursos de procesamiento para volver a calcular todos los valores de la columna Beneficio. No necesitamos realmente tener 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 categorías de productos, región o por 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 del total de ventas. Creamos una columna calculada denominada % de ventas en nuestra tabla Ventas, como esta:

Nuestra fórmula indica: Para cada fila de la tabla Ventas, divida el importe de la columna SalesAmount por el total SUMA 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 colocarla en VALORES, se obtiene un total de suma de % de ventas para cada una de nuestras categorías de productos.

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

A primera vista, esto puede parecer correcto. Sin embargo, nuestros porcentajes deberían llegar al 100 %, ya que queremos conocer el porcentaje de ventas totales de cada una de nuestras categorías de productos para 2007. ¿Qué ha salido mal?

Nuestra columna % de ventas calculó un porcentaje para cada fila que es el valor de la columna SalesAmount dividido por el total de suma de todos los valores de la columna SalesAmount. Los valores de una columna calculada se fijan. 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 % de ventas calculada 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 creamos anteriormente, la que simplemente suma la columna SalesAmount? Lo usamos como argumento en nuestra medida beneficio total y lo volveremos a usar 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 suma de SalesAmount sin ningún filtro de columna o fila distinto de los definidos en la tabla dinámica.

Ahora, si agregamos nuestro nuevo % de ventas totales a la tabla dinámica, se obtiene lo siguiente:

Se ve mejor. Ahora nuestro % de ventas totales para cada categoría de producto se calcula como un porcentaje de las ventas totales del año 2007. Si seleccionamos un año diferente o más de un año en la segmentación de datos CalendarYear, se obtienen nuevos porcentajes para nuestras categorías de productos, pero nuestro total general sigue siendo el 100 %. También podemos agregar otras segmentaciones de datos y filtros. Nuestra medida % de ventas totales siempre producirá un porcentaje de 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, y por los filtros o segmentaciones de datos que se aplican. Este es el poder de las medidas.

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

Usar columnas calculadas

• Si desea que los nuevos datos aparezcan en FILAS, COLUMNAS o 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 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 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, =FORMATO('Fecha'[Fecha],"mmmm") nos proporciona el nombre del mes de cada fecha en la columna Fecha de la tabla Fecha.

Usar medidas

• Si el resultado del cálculo siempre dependerá 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 a año o varianza, use un campo calculado.

• Si desea mantener el tamaño del libro al mínimo y maximizar su rendimiento, cree la mayor cantidad de cálculos posibles. 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 Beneficio y, a continuación, agregarla en una tabla dinámica o informe. En realidad, es una forma muy buena y fácil de aprender y crear sus propios cálculos. A medida que su comprensión de estas dos características extremadamente eficaces de Power Pivot va creciendo, querrá crear el modelo de datos más eficiente y preciso que pueda. Esperemos que lo que ha aprendido aquí le ayude. Hay otros recursos realmente excelentes que también pueden ayudarte. Estos son solo algunos: Contexto en fórmulas de DAX,agregaciones en Power Pivoty centro de recursos de DAX. Y, aunque es un poco más avanzado y dirigido a los profesionales de contabilidad y finanzas, el modelado y análisis de datos de pérdidas y ganancias con Microsoft Power Pivot en Excel muestra está cargado con excelentes ejemplos de fórmulas y modelado de datos.