Cuando aprende por primera vez a usar Power Pivot, la mayoría de los usuarios descubren que el potencial está agregando o calculando un resultado de alguna forma. Si los datos tienen una columna con valores numéricos, puede agregarlos fácilmente seleccionándolo en una lista de campos de tabla dinámica o de Power View. Por defecto, como es numérico, se sumará, calculará de forma continuada, recontará o cualquier tipo de agregación que seleccione. Esto se conoce como medida implícita. Las medidas implícitas son ideales para agregar de forma rápida y sencilla, pero tienen límites, y esos límites casi siempre se pueden superar con las medidas explícitas y las columnas calculadas.
Las medidas, son cálculos que se usan en el análisis de datos. Entre los ejemplos que suelen encontrarse en los informes empresariales se incluyen sumas, promedios, valores mínimos o máximos, recuentos o cálculos más avanzados que crea con una fórmula de expresiones de análisis de datos (Dax).
Una columna calculada le permite agregar nuevos datos a una tabla en el modelo de datos de Power Pivot, o Power BI.
Vamos a trabajar con el modelo de CONTOSO, vamos a traernos el modelo, para ello, en una hoja en blanco, vamos a la pestaña de Power Pivot, y, hacemos clic en administrar.
En la pestaña de inicio, dentro del grupo, obtener datos externos, hacemos clic en de otros orígenes.
Nos movemos hasta el final, y, seleccionamos, archivo de Excel, hacemos clic en siguiente.
Se abre la ventana, asistente para la importación de tablas, donde hacemos clic en examinar, para seleccionar nuestro archivo.
Marcamos, usar primera fila como encabezado de columna.
Hacemos clic en siguiente.
En la siguiente ventana, aparecen todas las tablas, seleccionamos las nuestras, y, hacemos clic en finalizar.
Nos aparece una ventana, donde nos dice que todo es correcto.
Hacemos clic en cerrar, y, tenemos nuestras tablas cargadas en Power Pivot.
Vamos a la vista de diagrama, y, estas son las relaciones que debemos de realizar.
Echemos un vistazo primero a un ejemplo, en el que usamos una columna calculada para agregar un nuevo valor de texto, para cada fila de una tabla denominada Ventas.
Cada fila de la tabla Ventas contiene todo tipo de información sobre las ventas de un producto.
Tenemos otra tabla relacionada denominada Promocion que contiene una columna TipoPromocion. Lo que queremos es que todos los productos de la tabla de Ventas incluyan el nombre del TipoPromocion de la tabla Promocion.
En nuestra tabla de Ventas, podemos crear una columna calculada, denominada Tipo de promoción, como esta:
Donde vamos a usar la función DAX, llamada Related, esta función, nos va a devolver un valor, relacionado con otra tabla, su único argumento es la tabla para utilizar, esta función requiere que ambas tablas estén relacionadas, en esta ocasión, lo están, pues, después del signo igual, ponemos la función RELATED, abrimos un paréntesis, y, ponemos la columna TipoPromocion de la tabla Promocion.
=RELATED (Promocion [TipoPromocion])
Vemos en la columna nueva, los nombres de los tipos de promoción.
Este es un excelente ejemplo, de cómo podemos usar una columna calculada para agregar un valor fijo para cada fila, que podamos 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 ver otro ejemplo, en el que queremos calcular un margen de beneficio para nuestras ventas.
Este es un escenario común.
Tenemos una tabla de ventas en nuestro modelo de datos con datos de transacción, y, existe una relación entre la tabla de ventas y la tabla de categorías de productos.
En la tabla de ventas, tenemos una columna, que contiene el precio unitario, y, otra columna contiene el costo unitario.
Podemos, crear una columna calculada, que calcula un importe de beneficio para cada fila, restando los valores de la columna CostoUnitario, de los valores de la columna PrecioUnitario, de la siguiente manera:
=[PrecioUnitario]-[CostoUnitario]
Nuestra columna Beneficios, no proporciona información útil, cuando se coloca en áreas de columnas, filas o filtros, solo tiene sentido como un valor agregado en el área valores.
Lo que hemos hecho, es crear una columna denominada Beneficios, que calcule un margen de beneficio para cada fila de la tabla Ventas.
Vamos a crear otra columna calculada, donde nos vamos a traer la columna calculada de beneficios, para ello, escribimos lo siguiente:
=[Beneficios]
Tenemos una copia de la columna beneficios.
Ahora, podemos crear una tabla dinámica, para ello, desde la pestaña de inicio, desplegamos tabla dinámica, y, seleccionamos tabla dinámica.
Se abre la ventana, crear tabla dinámica, seleccionamos nueva hoja de cálculo, y, aceptamos.
En la zona de campos, vemos todas las tablas.
Arrastramos el campo categoría de la tabla CategoriaProducto a filas, y, nuestro nuevo campo Beneficios de la tabla ventas, en el área valores (una columna de una tabla en Power Pivot es un campo de la lista de campos de tabla dinámica).
Tenemos una tabla dinámica, con los beneficios, que es una cantidad agregada de valores, de la columna suma beneficios, de cada categoría de producto, donde, el resultado es una medida implícita, denominada suma de beneficios.
A continuación, agregamos beneficios2, al área valores de la tabla dinámica, y, se crea automáticamente una medida implícita, en la que se calcula un resultado para cada una de las categorías de producto.
Donde tenemos los mismos valores en ambas columnas.
Si estás pensando, que realmente calculamos beneficios, para nuestras categorías de productos dos veces, es correcto, en primer lugar, calculamos un beneficio para cada fila de la tabla ventas y, después, agregamos beneficios2 al área valores, donde se agregó para cada una de las categorías de producto. Si también estás pensando, que no necesitamos crear la columna calculada de beneficios, también es correcto. Pero ¿Cómo se calcula la ganancia sin crear una columna calculada de ganancias?
Los beneficios, realmente se calcularían mejor como una medida explícita.
Por ahora, vamos a dejar la columna calculada de beneficios de la tabla de ventas, en valores, y, la categoría de producto en filas y en los valores de la tabla dinámica, para comparar nuestros resultados.
En el área de cálculo, de nuestra tabla de ventas, vamos a crear una medida denominada beneficiototal (para evitar conflictos de nombres), para ello, nos colocamos en una celda, dentro del área de cálculo, en la columna de total, vamos a la pestaña de inicio, dentro del grupo, cálculos, hacemos clic en autosuma. Al final, obtenemos los mismos resultados que lo hicimos antes, pero sin una columna calculada de beneficios.
Como resultado, tenemos:
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 PrecioUnitario, y, PrecioCoste.
A continuación, en el área de cálculo, restamos ambas medidas.
Como resultado, tenemos:
Creamos una nueva columna calculada, en la tabla ventas, la llamamos, beneficios totales, y, ponemos el nombre de la medida implícita Beneficios Total.
Puede ver, que la nueva medida Beneficio Total, devuelve los mismos resultados, que si crea una columna calculada de beneficios, a continuación, volvemos a la tabla dinámica, y, colocamos en valores, la medida beneficio total.
Vemos, que tenemos los mismos resultados.
La diferencia es que nuestra medida de beneficio total es mucho más eficaz, y, hace que nuestro modelo de datos sea más limpio, y, claro porque estamos calculando en el momento, y, solo para los campos que seleccionamos para la tabla dinámica. No necesitamos la 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 ocupan memoria.
Si actualizamos el modelo de datos, también es necesario procesar los recursos para volver a calcular todos los valores de la columna beneficios.
En realidad, no tenemos que ocupar recursos como este, porque, realmente deseamos calcular el beneficio cuando seleccionamos los campos en los que deseamos beneficios en la tabla dinámica, como los productos, la región o por fechas.
Echemos un vistazo a otro ejemplo. Una columna calculada, crea resultados que, al primer vistazo, parecen ser correctos.
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 de ventas, donde, dividimos la columna total, entre la suma de la columna total, vemos que, en una de las operaciones, usamos la función SUM, ya sabemos, que Power Pivot, trabaja por columnas, quiere decir, que cuando creamos una función, o, formula, se aplica a toda la columna, entonces, cuando en el primer argumento, [Total], quiere decir, el total de la columna, y, en el segundo argumento, vemos SUM([Total]), donde va a realizar la suma fila a fila, entonces, el total de la columna, será dividido entre cada valor.
Vamos a hacer uso de la función DIVIDE.
Como es un porcentaje bajo, damos al menor 5 decimales, como resultado, tenemos:
Vamos a crear una nueva tabla dinámica, donde nos llevamos a filas, de nuevo categoría de producto, y, a valores la columna % de ventas.
Obtenemos una suma total de % de las ventas para cada una de las categorías.
Volvemos al modelo en Power Pivot, en la tabla ventas, vamos a añadir una nueva columna calculada, donde vamos a usar la función YEAR, para extraer el año de la columna fechapedido.
Ahora, agregamos una segmentación de datos, para ello, con una celda dentro de la tabla dinámica, vamos a la pestaña analizar tabla dinámica, dentro del grupo filtrar, hacemos clic en segmentación de datos.
Se abre la ventana, insertar segmentación de datos, donde vemos nuestras tablas, pues, marcamos años, de la tabla ventas, y, aceptamos.
A continuación, marcamos un año.
A primera vista, es posible que siga apareciendo correctamente, pero, nuestros porcentajes deberían sumar realmente el 100%, porque queremos conocer el porcentaje de ventas totales de cada una de nuestras categorías de productos para 2020. Entonces, ¿Qué ha fallado?
Nuestro % de la columna de ventas, calculó un porcentaje por cada fila que sea el valor de la columna total dividido por la suma total de todos los valores de la columna total. Los valores de una columna calculada son fijos. Se trata de un resultado inmutable para cada fila de la tabla. Cuando añadimos % de ventas a la tabla dinámica, se agregaba como una suma de todos los valores de la columna total. Esa suma de todos los valores de la columna % de ventas será siempre de 100%.
Podemos eliminar la columna calculada % de ventas, porque no nos va a ayudar. En su lugar, vamos a crear una medida, que calcule correctamente el porcentaje de ventas totales, independientemente de cualquier filtro o segmentación de la aplicación que se haya aplicado.
Crear medidas explícitas, no solo son útiles en una tabla dinámica o un informe, también son útiles como argumentos en otras medidas cuando se necesita el resultado como argumento. Esto hace que las fórmulas sean más eficientes y fáciles de leer. Esta es una buena práctica de modelado de datos.
Creamos una nueva medida, pero, lo vamos a hacer, desde la pestaña de Power Pivot, por lo que volvemos a la hoja de cálculo, dentro de la pestaña Power Pivot, desplegamos medidas, y, seleccionamos nueva medida.
Se abre la ventana medida, donde desplegamos nombre de la tabla, y, seleccionamos la tabla ventas, que es donde vamos a crear la medida.
Damos un nombre a la medida.
En la ventana formula, es donde vamos a introducir nuestra función, vamos a usar la función DIVIDE, como primer argumento, usamos la función SUM, para sumar la columna de total, ponemos punto y coma, para pasar al siguiente argumento, donde vamos a usar la función CALCULATE, donde como primer argumento, volvemos a sumar la columna de total, y, como segundo argumento, usamos la función ALLSELECTED, esta función va a ignorar cualquier filtro aplicado.
Hacemos clic en comprobar formula, y, vemos que no hay errores.
Aceptamos.
Vemos como se ha agregado a la zona de campos, dentro de la tabla ventas.
Esta fórmula indica: dividir el resultado del total venta por la suma total de venta sin ningún filtro de columna o fila distinto de los definidos en la tabla dinámica.
Vamos a quitar de valores, el campo que tenemos, el de porcentajes, y, ponemos la media.
Podemos ver que ahora el porcentaje es el 100%, es decir, corresponde con los datos que vemos.
Si filtramos por otro año, veremos que los porcentajes cambien, pero el total es el 100%.
Con las medidas, el resultado siempre se calcula según el contexto determinado por los campos de columnas y filas, así como por cualquier filtro o segmentación de la 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 es la adecuada para un cálculo concreto necesita.
Usar columnas calculadas
Si desea que los datos nuevos 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 de datos normales, las columnas calculadas se pueden usar como campos en cualquier área y, si son numéricos, también se pueden agregar en valores.
· Si desea que los nuevos datos tengan 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 mes a partir de las fechas de la columna de fecha.
Por ejemplo, = MONTH (' fecha ' [Date]).
· 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 da el nombre del mes de cada fecha de la columna Date de la tabla Date.
Usar medidas
· Si el resultado de los cálculos siempre depende de los otros 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 por año o varianza, use un campo calculado.
· Si desea mantener al mínimo el tamaño de un libro y maximizar su rendimiento, cree tantos cálculos como sea posible. En muchos casos, todos los cálculos pueden ser medidas, reducir significativamente el tamaño del libro y acelerar el tiempo de actualización.
Tenga en cuenta que no hay ningún problema con la creación de columnas calculadas, como hicimos con nuestra columna beneficios y, después, la agrega en una tabla dinámica o un informe. En realidad, es una forma realmente buena y sencilla de aprender y crear sus propios cálculos. A medida que su conocimiento de estas dos características muy eficaces de Power Pivot crece, querrá crear el modelo de datos más eficaz y preciso que puede.
Comentarios