Guía de Introducción a Medidas en Power Pivot
Las Medidas en Power Pivot (Denominados Campos Calculados en Excel 2013) son uno de los dos de los tres tipos de cálculos personalizados o cálculos DAX que están a nuestra disposición para crear soluciones mediante Excel (y otras tecnologías de Microsoft Business Intelligence) sin ninguna limitación.
No se exagera cuando hay personas que dicen que: «Es lo mejor que le ha pasado a Excel en su historia» o que «Representa un cambio total en las reglas del juego a nuestro favor» Todo tipo de cosas se dicen de Power Pivot y no es por darle un valor desorbitante, ¡De Verdad que no! Yo Personalmente Digo Que:
¡Componen una excelsa sinfonía de una revolución mágica y sin precedentes para Business Intelligence -BI-! es como si nos hubieran traído una tecnología de veinte años en el futuro para utilizarlos hoy en día. La capacidad de resumir cantidades masivas de datos, crear expresiones de todo tipo para extraer gemas de información, acompañado de la posibilidad de crear reportes de tablas dinámicas como nunca antes se había soñado, la convierten el arma letal para inteligencia de negocios.Business Intelligence al alcance de todos.
El núcleo de Power Pivot y en concreto del espléndido lenguaje DAX radica en sus tres tipos de cálculos DAX, uno de ellos son las MEDIDAS que estudiaremos el día de hoy
¿Qué es una Medida o Campo Calculado para Power Pivot?
Demos una definición sencilla pero que capte la esencia de qué es una medida:
Una Medida es una fórmula que se añade a un reporte de tabla dinámica.
Es vital antes de continuar resaltar la diferencia de nombres en las distintas versiones, este tipo de cálculo DAX se ha denominado de diferentes formas en las diversas versiones de Excel y otros programas, así:
- Excel 2010: Medidas (En Inglés Measures)
- Excel 2013: Campos Calculados (En Inglés Calculated Fields)
- Excel 2016: Medidas
- Power BI / SSAS/ SQL Tabular: Medidas
.
En definitiva, Microsoft opto, pero renombrar al tipo de cálculo que añade fórmulas a les reportes de tablas dinámicas como Campos Calculados en Excel 2013. Sin embargo, volvió a su nombre original, ya que ayuda a marcar una diferencia desde el principio en contraste con la funcionalidad antigua de Campos Calculados.
Campos y Elementos Calculados (Funcionalidad Antigua de las Tablas Dinámicas)
Es cierto que las tablas dinámicas nos han brindado funcionalidades para ñadir fórmulas personalizadas a los reportes, funcionalidad conocida como campos calculado, sería el «homólogo» de las Medidas (De allí que lo renombran como Campo Calculdo en el 2013. Una muy mala idea en todo caso)
De hecho, tenemos un vídeo dedicado completamente a las antiguas funcionalidades de Campos y Elementos Calculados
¿Cuál es la diferencia?
En resumen, tenemos «dos» Campos Calculados para Tablas Dinámicas (siguiendo el nombre que tuvieron en común en el 2013):
Campos Calculados derivado de las funcionalidades clásica de tablas dinámicas y Campos Calculados (o Medidas como han pasado a llamarse universalmente) del lenguaje DAX, Power Pivot.
– Campos Calculados Funcionalidad Clásica de Excel
Los campos calculado en esta modalidad «clásica», nos permiten crear cálculos nuevos basados en campos existentes, sin embargo, estas opciones «clásicas» traen más dificultades y limitaciones que posibles soluciones.
Para no dar rodeos: Las fórmulas y operaciones que se pueden utilizar son bastante limitadas (Por no decir que nulas) además con restricción en operaciones, las fórmulas que en esta funcionalidad están 100% separadas del motor clásico de fórmulas de Excel.
En realidad, Microsoft nunca ha invertido gran presupuesto en el desarrollo de esta funcionalidad (siempre hay que sopesar donde pasan más tiempo los usuarios de Excel), por lo que enfatizando nuevamente: lo que podemos hacer con dicha funcionalidad tiende a cero.
La funcionalidad clásica de campos calculados de tablas dinámicas nunca fueron de gran utilidad, por lo que, a menudo era más una curiosidad que una características que los usuarios de Excel aplicarán en el trabajo y, para aquellas personas que los utilizan y agradan, los cálculos DAX cambiaro por competa su forma de ver el mundo de tratamiento y análisis de datos.
Sin temor a exagerar, esta funcionalidad se seguirá mantenido por compatibilidad para las versiones futuras, más que por su uso.
– Campos Calculados [MEDIDAS] en Power Pivot
Los campos calculados en Power Pivot nos permite hacer cualquier cosa, por ejemplo:
- Crear expresiones para análisis de clientes nuevos, recurrentes y perdidos que nos permitan analizar los clientes recurrentes
- Análisis de afinidad en productos
- Análisis ABC
- Análisis de inventarios, lead time y distribución
.
… Por mencionar unos pocos …
Cualquier tipo de resumen que se nos ocurra, pues para ello tenemos a nuestra disipación todo lenguaje de fórmulas bien estructurado y completado, denominado: Lenguaje DAX.
Hemos llamado a las fórmulas que se añaden a un reporte como Campos Calculados, de ahora en adelanta siempre las denominaremos por su nombre universal: MEDIDAS
Ahora entendemos la diferencia y su desemejanza abismal
Crear Medidas en Power Pivot
Las Medidas se pueden agregar en dos partes, desde Excel o desde la venta de Power Pivot.
En Excel podemos añadir una medida a partir del cuadro de diálogo medidas, mientras que en la ventana de Power Pivot podemos añadir medidas desde el área de medidas o desde la barra de fórmulas DAX.
– Habilitar Power Pivot y Tabla de Datos
Demos un vistazo a la tabla que utilizaremos hoy:
Lo primero es habilitar el complemento de Power Pivot y añadir la tabla al modelo de datos, el primer vídeo de la mini serie nociones en Power Pivot ilustra cómo.
¡Es importante conocer como habilitar Power Pivot y como añadir tablas al Modelo de Datos / Power Pivot! Si no sabemos cómo, No dejes de ver el vídeo
Utilizaremos las tablas dinámicas para trabajar con Power Pivot y resumir datos, ya que es su núcleo principal, no obstante, no quiere decir que el lenguaje DAX y Power Pivot este atado únicamente a tablas dinámicas, NO. Es más bien la manera más extendida y familiar para entrar en esta funcionalidad.
Por lo anterior debemos saber cómo decirle a Excel que vamos a crear una tabla dinámica que utilice Power Pivot, el modelo de datos.
– Tabla Dinámica con Power Pivot Excel 2016
En el artículo donde traté brevemente lo nuevo en Tablas Dinámicas de Excel 2016, comenté que ahora podemos crear una tabla dinámica desde el vacío, es decir, podemos situarnos en celda vacía pulsar clic en el comando Tablas Dinámica y crear un reporte a partir de las tablas internas cargadas previamente en Power Pivot.
Para decirle que vamos a utilizar la tablas añadidas a Power Pivot solamente seleccionamos la opción: Usar el modelode datos de este libro.
Esta opción es nueva para Excel 2016, por lo que para Excel 2013 7 2010 debemos optar por el camino clásico.
No hay manera de tener Power Pivot en Excel 2007 y anteriores.
– Tabla Dinámica con Power Pivot Excel 2013
Para Excel 2013 debemos ir a la ventana de Power Pivot. Pestaña POWER PIVOT, comando ADMINISTRAR.
Una vez allí desplegamos las opciones de tabla dinámica y clic en tabla dinámica.
– Crear una Medida en Excel con el Cuadro de Diálogo
Para crear el reporte de tablas dinámica llevemos el campo SKU de la tabla Pedidos:
Bien, supongamos que por el momento solo queremos conocer el promedio de ventas para cada uno de los SKU.
Con Power Pivot necesitamos crear una medida, para ello vamos a la pestaña Power Pivot, y en el grupo Cálculos desplegamos las opciones de medida para finalmente pulsar clic en Nueva Medida.
Con la acción anterior se despliega el cuadro de diálogo Medidas:
Nombre Tabla:
Lo primero es seleccionar la tabla, como regla general ponemos la tabla dependiendo dela fórmula DAX creada, básicamente corresponde a la misma tabla de los campos numéricos que utilicemos en la expresión DAX.
Para nuestro caso como vamos a utilizar un campo de la tabla Pedidos y numéricos, además, entonces debemos seleccionar la TABLA Pedidos en la Lista Desplegable
Nombre Medida:
Nada extraño aquí, solamente poner un nombre descriptivo (Se pueden utilizar espacios)
Fórmula:
En esta área escribimos la expresión, en esencia esta zona funciona de igual manera al funciones clásica de Excel, ponemos un signo igual y digitamos el nombre de la función que queremos utilizar, como en este caso es PROMEDIO debemos escribir AVERAGE, igualmente contamos con el intellisense para apoyarnos.
El idioma de las funciones del lenguaje DAX es inglés, sin importar la configuración regional que tengamos predeterminada; de la misma manera, Power Pivot cuenta con sus propias configuraciones de idioma, a pesar de ello, estas aplican únicamente a los elementos de la interfaz de usuario y no para el lenguaje DAX. Si encontramos una alternativa o Microsoft proporciona las equivalencias, se los haremos saber.
No conocemos una manera, bien sea con un complemento o pacñ qu lo permita, si tu mi amigo lector conoces de algo, te pedimos de la manera más comedida que nos lo hagas saber.
Cuando pulsamos clic en Aceptar, vamos a encontrar la medida en la parte inferior de la tabla en el panel de campos, entonces debemos llevar la medida al área de valores
Con lo cual nuestra tabla dinámica queda de la siguiente manera:
Estarás pensado, Ok, interesante pero ese resultado lo puede lograr con tan solo arrastrar el campo Precio de Ventas al área de valores y cambiar la función de resumen a promedio.
Y tienes toda la razón. Pero …
¿Qué sucede si nuestro análisis no funciona con la medida de tendencia central aritmética?
Por ejemplo, supongamos que necesitamos promediar porcentajes, sabemos por estadística que un cálculo que se ajusta mejor es la media geométrica.
¿Cómo hacer?
Dado que la media geométrica no es una función de resumen de las tablas dinámica, el lenguaje DAX llega al rescate.
El lenguaje DAX nos proporciona todo un espectro de funciones, en las cuales por supuesto, se encuentra la media geométrica y muchas otras funciones estadísticas.
– Media Geométrica Para Resumir Valores En Tabla Dinámica
Con lo cual la tabla dinámica quedaría:
Y eso es solo un ejemplo de los más sencillo, pero podemos crear fórmulas DAX tan robustas como esta:
=
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( Sales[CustomerKey] ),
«PreviousSales», CALCULATE (
COUNTROWS ( Sales ),
FILTER ( ALL ( ‘Date’ ), ‘Date'[FullDate] < MIN ( ‘Date'[FullDate] ) )
)
),
[PreviousSales] = 0
)
)
La cual nos permite anlaizar el comportamiento de los clientes.
Algunos Beneficos de las Medidas
Con el ejemplo anterior puedes ver el poder del lenguaje DAX para resumir datos de todo tipo y mostarlos como necesitemos sin ninguna restricción. Sin embargo, en los caso que solo usamos cálculos simples como: SUMA, PROMEDIO, DESVIACIÓN ESTÁNDAR y CONTAR, puede ser tentador utilizar las funciones de resumen clásicas, no obstante, quiero recomendarte el tercer vídeos de la mini-serie: Nociones en Power Pivot paa que veas algunos beneficios adicionales.
Bien, esto es sólo el principio, pero de momento todo por el artículos
¡Muchas gracias y hasta la próxima!