Es bastante habitual en el grupo de Power BI Español peticiones de información sobre medidas de nivel medio avanzado que a los usuarios que empiezan les cuesta resolver.
Voy a poner un ejemplo de un problema bastante habitual que se puede resolver con tablas calculadas, pero que habitualmente los usuarios en un nivel inicial no son capaces de entender por la dificultad que entraña conceptos como el de contexto, dinamización etc. muy habituales en DAX.
Vamos a analizar la medida que se conoce como same store sales, pero desde el requerimiento de negocio siguiente que nos propuso el usuario del grupo de Telegram.
El Same Store Sales por definición es el análisis de las ventas entre un período y las ventas del mismo período anterior, pero teniendo en cuenta sólo las tiendas existentes en ambos períodos.
El requerimiento que nos presentaba el usuario en este caso era hacer lo mismo, pero con productos y no tiendas y, por otro lado que la comparativa fuera de ventas mensualizadas, es decir, comparara las ventas producto a producto de este año con respecto al año anterior mensualizadas y que se diera la condición de que el producto tuviera ventas en este ejercicio-mes del año del contexto y en el mismo período del ejercicio anterior.
Lo primero que se le podría ocurrir a cualquier usuario es construir la medida con IF.
Same Product Sale Monthly con IF
IF([Ventas]&&[Ventas PY], [Ventas]-[Ventas PY], BLANK())
¿Qué ocurre con esta medida, es correcta? Como yo siempre advierto, IF es una fórmula muy mentirosa, porque no filtra, sino que oculta la información
Como podemos observar en la foto, aunque a nivel de línea, para aquellos productos que no tuvieron ventas en el ejercicio anterior no muestra valor, sin embargo, a la hora de totalizar es otra historia, porque en el contexto del mes no existe producto individual sino “todos los productos” y por ello, el total coincide con el total de la diferencia entre Ventas y Ventas PY obtenido de la resta de esas medidas
¿Cómo podemos llegar al cálculo correcto? Yo siempre digo que, si podemos representar lo que queremos en una tabla, podemos calcularlo. En el caso anterior tenemos la tabla en la imagen anterior, necesitamos mensualizadas las ventas de cada producto y que sólo nos devuelva el valor si hubo ventas este año y en el mismo mes del año anterior, pero necesitamos filtrar para que no nos aparezcan el resto de los elementos a la hora de hacer nuestro cálculo.
Pues bien, vamos manos a la obra…
1º Vamos a crear una variable con la reproducción de esa tabla de la siguiente manera:
VAR __ventasmensualizadasporarticulo =
SUMMARIZE(
CROSSJOIN(
VALUES(Fechas[Ejercicio-Mes]),
VALUES(Productos[ProductName])),
Fechas[Ejercicio-Mes],
Productos[ProductName],
"___Ventas", [Ventas],
"___VentasPY", [Ventas PY])
Estamos haciendo una tabla sumarizada que incluye todas las combinaciones (CROSSJOIN) de Ejercicio-Mes y Product Name con sus correspondientes Ventas y Ventas PY
2º Filtramos esa tabla con los requisitos que nos propone negocio:
VAR __tablafiltrada =
FILTER( __ventasmensualizadasporarticulo,
[___Ventas]>0 && [___VentasPY]>0)
Que las ventas sea >0 en el contexto, dado que tenemos esa cantidad contextualizada por producto y ejercicio mes y que las Ventas PY en ese contexto sean >0
3º y 4º Dos variables nuevas para iterar la tabla filtrada sumando cada una de sus columnas Ventas y Ventas PY. Al tratarse de una tabla calculada en memoria, no podemos simplemente hacer un SUM de las columnas de la tabla calculada virtual, no son columnas propiamente dichas, por lo que tendremos que utilizar el iterador SUMX sobre la variable __tablafiltrada y en esta iteración de la tabla virtual si nos va a permitir sumar la columna virtual
VAR __VentasTablaFiltrada =
SUMX(__tablafiltrada, [___Ventas])
VAR __VentasPYTablaFiltrada =
SUMX(__tablafiltrada, [___VentasPY])
Y por último en el return ya podremos restar a las Ventas de la tabla filtrada las ventas del periodo anterior de la tabla filtrada e incluso hayar el porcentaje si lo necesitáramos.
La medida definitiva sería la siguiente:
/* Diferencia de ventas del período del contexto con respecto al ejercicio anterior a nivel de producto */
/* teniendo en cuenta que debe cumplir la condición de que se haya vendido a nivel del mes, en el mes analizado */
/* y también en el mismo mes del ejercicio anterior. Si la venta no se produce para el mismo artículo en */
/* los dos meses comparados se excluye del análisis.*/
/* Autor: Francisco Mullor */
VAR __ventasmensualizadasporarticulo =
SUMMARIZE(
CROSSJOIN(
VALUES(Fechas[Ejercicio-Mes]),
VALUES(Productos[ProductName])),
Fechas[Ejercicio-Mes],
Productos[ProductName],
"___Ventas", [Ventas],
"___VentasPY", [Ventas PY])
//En esta variable estamos creando la tabla completa cruzada de Ejercicio-Mes y Product Name con el Total de Ventas y Ventas del mismo mes del año anterior en ese contexto de mes y producto
VAR __tablafiltrada =
FILTER( __ventasmensualizadasporarticulo,
[___Ventas]>0 && [___VentasPY]>0)
//En esta variable estamos filtrando la tabla cruzada anterior con los requisitos que nos planteaban: a) Que tuviera el producto tuviera ventas en el mes del contexto y b) Que tuviera ventas el mes del año anterior al contexto
VAR __VentasTablaFiltrada =
SUMX(__tablafiltrada, [___Ventas])
// En esta variable estamos iterando a través de la tabla filtrada sumando la columna ventas
VAR __VentasPYTablaFiltrada =
SUMX(__tablafiltrada, [___VentasPY])
// En esta variable estamos iterando a través de la tabla filtrada sumando la columna ventas del periodo anterior
RETURN
__VentasTablaFiltrada - __VentasPYTablaFiltrada
Y este el resultado que nos arroja para una subcategoría concreta, los Microondas.
Evidentemente como podemos observar en este sector tiene muy poco sentido el análisis que hemos hecho. Esto es algo que tenemos que tener muy en cuenta cuando planteamos la realización de un KPI. En un sector como el de los electrodomésticos, tendrá muy poco sentido comparar a nivel de producto las ventas con respecto a las ventas del año anterior dado que las referencias están en continua evaluación y el producto que se vendió el año pasado seguramente este año esté ya descatalogado.
En todo caso podemos llegar a través de la creación de esta medida a las siguientes conclusiones:
1º Hay que tener mucho cuidado cuando empleamos la función IF ya que esta no filtra, sino que oculta información en un contexto y por lo tanto en contextos de totalización no funcionará nunca.
2º Si puedo representar lo que quiero en una tabla, puedo llegar al cálculo mediante tablas calculadas en variables, sólo tengo que conocer las funciones de tabla existentes, SUMMARIZE, FILTER, CROSSJOIN, DISTINCT, VALUES, EXCEPT… son algunas de ellas. Con las funciones de tabla podremos recrear los contextos en los que necesitamos realizar nuestros cálculos.
Un libro muy interesante que utiliza muchísimo el concepto de Tabla calculada y la iteración a través de las mismas es DAX Cookbook de Greg Deckler en el que desmitifica muchísimo el uso del CALCULATE y da otro enfoque totalmente distinto y perfectamente válido al uso de DAX mediante tablas calculadas e iteradores.
Como siempre digo, en DAX, muchos caminos conducen a Roma.
5 comentarios