Cada día los analistas conocen más y más de DAX junto con sus contextos. Ésto lleva a resolver medidas en modelados más complejos. Lo cierto es que los requerimientos de los usuarios suelen ser mucho más que una sencilla regla de negocio que viene resuelta en un buen data modeling.
Tanto los proyectos de un buen data modeling como de aquellos de muchos excels exportados, los pedidos para la construcción de una matemática de finanzas o de lógicas en el negocio complejas hacen que la búsqueda por una medida eficiente que necesitamos se resuelva sobre el contexto en el que está corriendo considerando al total su propio contexto. Este problema se está volviendo cada vez más recurrente.
A continuación vamos a ver una muy sencilla medida que ejemplifica el error de totales que desearía un usuario. No es necesario entender la siguiente medida puesto que es un invento de un escenario antiguo. Si quieren pueden pensar la medida como "SUM(Tabla[Columna]) + 10″ y verán el escenario que reproducimos con el problema en su total. La resolución del total será independiente de la siguiente medida. El ejemplo siguiente quiere medir la proyección de cantidad de ventas del año que viene y lo hará sumandole la resta contra la venta promedio del mes a cada mes:
Proyeccion =
VAR prom_mensual =
DIVIDE(
CALCULATE(SUM(InternetSales[Quantity]),
ALLEXCEPT(TablaFecha, TablaFecha[Año]))
, 12)
VAR total = SUM(InternetSales[Quantity])
RETURN
total + ABS(total - prom_mensual)
En distintos artículos vemos que el uso de calculate de forma tal nos ayudaría a mejorar la performance de nuestra medida puesto que evitamos la iteración de la tabla principal involucrada. Sin embargo, depende del objetivo visual que tengamos para el cálculo. Veamos el resultado para tres meses.
Nuestra medida funcionaría perfecto para una evolución lineal:
Pero no podemos decir lo mismo de una tabla que refleja el total puesto que:
577 + 637 + 753 = 1967
En realidad DAX ejecuta la medida para su contexto de totales, entonces hace algo como
1844 + [PromedioMensual] = 3111
El total tiene su propio contexto con lo cual la medida ejecutaría con los números del Total aquello que escribimos en DAX. Es por ello que no coinciden.
Para este tipo de casos que son cada vez más frecuentes porque una medida compleja con cálculos a distintos contextos difícilmente sume sus valores mostrando el total deseado en cada contexto, vamos a crear una segunda medida que haga la operación que deseamos con la medida creada inicialmente y la operación de agregación total elegida.
Total Proyeccion =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
TablaFecha, TablaFecha[Año], TablaFecha[Mes]
)
, "@Proyeccion", [Proyeccion]
)
, [@Proyeccion]
)
De esta forma si agregamos la medida a la tabla veremos que los valores puntuales no cambian puesto que se resuelven para cada fila igual que antes y luego se suman para el total. En particular, estamos construyendo la tabla resultada a la que queremos sumar los valores de proyección mediante addcolumns con summarize. Usamos esa combinación de funciones por este post de SQLBI develando los secretos de las mismas. La notación @Nombre esta destinado a columnas nuevas virtuales como lo son las que creamos en una medida para utilizar luego.
Resultado final:
Espero que este caso les sea de utilidad para sus fallas en valores totales y no dejen de leer el post de explicación de summarize.