martes, 6 de abril de 2021

Power Pivot: Medidas y Funciones CUBO

En entradas previas del blog estuvimos practicando con funciones estándar como SUMAPRODUCTO (ver aquí) y las especiales funciones CUBO y el lenguaje MDX (ver aquí) para conseguir replicar el comportamiento de las funciones BD, es decir, poder operar sobre una base de datos a partir de un rango de criterios.

Hoy veremos una nueva opción trabajando con DAX dentro de Power Pivot para Excel.

En lugar de generar expresiones en MDX dentro de la función CONJUNTOCUBO para crear el conjunto de elementos requerido que cumpla las condiciones, crearemos dentro del Modelo de datos una Medida para recuperarla directamente con la función VALORCUBO.
Seguro que te sorprenderá :OO
Power Pivot: Medidas y Funciones CUBO

Ya teníamos cargada nuestra Tabla de Ventas ('Tabla1') en el Modelo de datos. A continuación repetiremos la acción y cargaremos la tabla de criterios ('TblCRITERIOS').
Y NO LA RELACIONAREMOS!!, es decir, vamos a trabajar con tablas NO relacionadas.
Este es un punto importante, y que aporta algo de complejidad a nuestra labor.

Este sería nuestro diagrama de relaciones en el Modelo:
Power Pivot: Medidas y Funciones CUBO

Aunque en teoría sería posible (y casi siempre recomendado) crear directamente la Medida en el Modelo, he de reconocer, que tras varios intentos me rendí y no pude lograrlo (ojalá algún experto en DAX me indique la forma), por lo que me apoyé en una columna calculada para lograrlo.

Así pues, dentro de la 'Tabla1' de ventas en el Modelo añadimos la siguiente columna calculada (que llamaré CRITERIOS) con la fórmula:
=VAR XX=
FILTER(TblCRITERIOS;
IF(TblCRITERIOS[Comercial]="";"1";TblCRITERIOS[Comercial]=(Tabla1[Comercial]))&&
IF(TblCRITERIOS[País]="";"1";TblCRITERIOS[País]=(Tabla1[País]))&&
IF(TblCRITERIOS[Producto]="";"1";TblCRITERIOS[Producto]=(Tabla1[Producto])))
RETURN
CALCULATE(COUNTROWS(TblCRITERIOS);XX)>0

Donde se cuenta el número de veces que los datos de Tabla1 cumplen los criterios de la TblCRITERIOS... devuelve TRUE si lo verifica al menos una vez.
Power Pivot: Medidas y Funciones CUBO

Obtendremos una columna de TRUE y FALSE, línea por línea, sabiendo si éstas cumplen alguno de los criterios de la tabla de criterios TblCRITERIOS.
Sobre la columna calculada ya operativa, generaremos una simple Medida:
SumCRIT:=SUMX(Tabla1;[Ventas]*[CRITERIOS])
Power Pivot: Medidas y Funciones CUBO

Con la medida 'SumCRIT' creada ya podemos volver a la hoja de cálculo y recuperar su información bien con tablas dinámicas (lo que haré para verificar el resultado devuelto) o bien con las funciones CUBO.
Power Pivot: Medidas y Funciones CUBO
Así en la celda S10 insertaremos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[SumCRIT]]";N10)
donde hacemos uso de nuestra recien generada medida, y nos apoyamos en el conjunto de años creado en la celda N8 con la función:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Calendar].[Date Hierarchy].&[2013]:[Calendar].[Date Hierarchy].&[2019]";
"Años-13_19")
y desplegados sus elementos en N10 hasta N16 con:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";N8;SECUENCIA(RECUENTOCONJUNTOCUBO(N8)))
visto y explicado en post previos...

Llegado el momento de las comprobaciones observamos como en todos los casos (tabla dinámica, SUMAPRODUCTO, MDX o Medidas) el resultado es coincidente... trabajando correctamente, evitando duplicidades, en caso de registros que cumplan varias condiciones simultaneamente.
Por ejemplo, si te fijas en la 'Tabla1' en la fila 14, puedes ver como cumple DOS de las condiciones:
-corresponde a ANA y ES
-tiene un P1
pero obviamente NO duplica el importe en los cálculos...

Una forma distinta de trabajar con datos cargados en Power Pivot, que exige un conocimiento alto del lenguaje DAX para construir nuestra medida... pero que, a cambio, ofrece seguridad en el cálculo.

OJO, por que trabajar empleando medidas, en lugar de expresiones MDX, nos exige una Actualización de datos!!

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.