Brújula Económico-Financiera: www.altair-consultores.com

Datos en información, información en conocimiento y conocimiento en ventajas competitivas

Mi nombre es Javier Ruiz y he desarrollado mi carrera dentro del área de la Dirección Financiera, Control de Gestión, Consultoría durante... bastante tiempo J

Durante todos esos años, el uso de las herramientas de EXCEL ha sido vital en el desarrollo de mi carrera: infinidad de cálculos, informes, análisis de datos de ventas, financieros, consolidación de cuentas de grupos de empresas, filiales, bases de datos, resultados, proyecciones financieras, de ventas, cuadros de mando financieros, comerciales, sistemas de información de costes, liquidación de impuestos, nóminas, .... y un sinfín de otros usos que, realmente han hecho mucho más fácil mi trabajo de lo que habría sido en el caso de que EXCEL no hubiera existido. Probé otras opciones alternativas, pero no fueron más que aventuras pasajeras que no fueron más allá de lo que fuera el primer obstáculo de cálculo o funcionalidad a utilizar cuya solución con EXCEL ya conocía, por lo que siempre volvía a los brazos de EXCEL. Desde hace muchos años, me considero un EXCEL PRO :-)

Podría afirmar sin temor a equivocarme que, EXCEL ha sido sin duda una de las mejores herramientas inventadas por el ser humano. Bueno vale... al menos en área de gestión, administración y finanzas. Aunque también en el área de la investigación en medicina, física, matemáticas y otras ciencias que ayudan a nuestra especie (y a otras), estoy seguro de que el uso de EXCEL es de vital importancia. Podemos dejar a debate esta teoría, aunque el desarrollo de los argumentos a favor o en contra, a pesar de su solidez que pudieran presentar, probablemente no cambiarían mi opinión.

Funciones de búsqueda y sumas condicionales, macros, tablas dinámicas y reportes dinámicos han sido la piedra angular de EXCEL. Pero cada mejora en cada versión no hacía más que acentuar el hecho de las limitaciones que presentaba siempre. Cuando los datos a tratar, excedían de una determinada cantidad, determinados cálculos, a pesar de la búsqueda de la excelencia en la optimización de los mismos, de aplanar distintas tablas y fuentes de datos en una, EXCEL se convertía en una pesadilla.... que seguramente muchos EXCEL pros hemos sufrido durante años.

A veces pensaba que, si me cruzara por la calle con Bill Gates, y tuviera la oportunidad de hacerle una pregunta, tendría muy claro cuál sería:

-Apreciado Bill, entendería las limitaciones en cuanto a la cantidad de datos a incorporar en un libro de Excel. Sé que tu equipo ha hecho un esfuerzo en aumentar hasta algo más de un millón el número de filas, aunque realmente el manejo de tal cantidad de datos en Excel se hace imposible. Habéis mejorado mucho en cuanto a funciones, gráficos, etc… pero ¿cuándo ampliareis las posibilidades que ofrecen los campos calculados en una tabla dinámica? Sería de agradecer – A lo que Bill Gates hubiera respondido – Apreciado Javier, te hablaría del proyecto Géminis pero no tengo permitido hacerlo.

CALCULATE

CALCULATE

Su funcionamiento

CALCULATE es con diferencia la función más potente de DAX y se encarga de evaluar una expresión en un contexto que está modificado por los filtros especificados en la función.

Con CALCULATE comenzarán la mayoría de las fórmulas que diseñemos en nuestros informes.

Con esta función podremos:

-      Añadir nuevos filtros o modificar al contexto existente

-      Ignorar una parte o todo el contexto de evaluación.

CALCULATE presenta una sintaxis muy sencilla:

CALCULATE(<expresión>;<filtro1>;<filtro2>…)

Una primera parte, la expresión que queremos calcular y evaluar, seguida de una serie de filtros que definirán el nuevo contexto modificado por CALCULATE en el que se evaluará la expresión. De ahí que CALCULATE resulte, con diferencia, la función más interesante de DAX.

En la sintaxis de CALCULATE la única parte obligatoria es la expresión, por lo que podremos expresar una función CALCULATE sin ningún filtro y funcionará sin problemas.

CALCULATE(<expresión>)

No podremos entender CALCULATE sin entender el contexto de evaluación y el contexto de transición, pero tampoco seremos capaces de entender estos últimos sin utilizar la función CALCULATE. Más adelante en este libro quedará explicado en qué consiste cada uno de estos conceptos por lo que, si no comprende cómo funciona alguna expresión o por qué se obtiene un determinado resultado es momento de que viaje al capítulo “El contexto de Evaluación y Contexto de transición” e intente comprender el motivo de su comportamiento. No limite el número de veces que tenga que revisar cada concepto, porque probablemente cuando vuelva de nuevo a esos capítulos descubrirá nuevas cosas interesantes.

Analicemos la siguiente expresión con la tabla de ejemplo.

Número de libros comprados por Mujeres=CALCULATE (SUM (Datos [Libros Comprados]); Datos[Género]="M")

CALCULATE realiza el siguiente proceso:

1. Analiza el contexto de valuación en el que se encuentra la expresión.
2. Si el filtro que incorpora CALCULATE ya está en la tabla dinámica lo anula y si no lo está, se une a él como nuevo filtro en el contexto de evaluación.
3. Ejecuta el cálculo de la expresión.

El paso número uno es habitual a todas las funciones en DAX. Analiza e incorpora el contexto de evaluación para el cálculo.

Es en el paso 2 cuando CALCULATE demuestra su potencial: Incorpora sus filtros al contexto, anulando para el filtro existente en el contexto que sea aportado por CALCULATE.

Es importante que recuerde la secuencia en la que se desarrolla todo el proceso como punto de partida para entender y diseñar nuevas fórmulas.

Podemos observar cómo se anula el contexto de evaluación cuando incorporamos nuevos filtros a la tabla dinámica como por ejemplo el género del cliente:

Nuestra expresión incorporaba el filtro de género por lo que, como este filtro ya existe en la tabla dinámica CALCULATE lo anula.

El resultado del cálculo es claramente erróneo para el género femenino “F” y para la línea del cliente con género desconocido. Independientemente del contexto que le venga dado en cuanto al filtro incorporado el resultado será el mismo.

Pero observemos sin modificamos en las etiquetas de fila el género por el nombre del cliente.

Observemos, además, qué ocurre si forzamos a que la tabla dinámica muestre también el resto de filas.

Analicemos el proceso que DAX realiza con el cliente llamado Andrés.

La mejor y más ilustrativa forma de analizar qué está ocurriendo es entrando a ver qué ocurre en la tabla durante todo el proceso. Este es el pensamiento de DAX, debe tener en cuenta que las operaciones de nuestras expresiones se realizarán en un modelo tabular en el que en función de los diferentes contextos y la transición de filtros se realizará la operación. Puedes aprovechar la capacidad de filtrado en la ventana de power pivot para definir usando los filtros de la tabla bajo qué condiciones se va realizar la operación.

1. Analiza el contexto de evaluación inicial: “Andrés”
2. CALCULATE impone su contexto de filtro para género, que es “M” pero como este filtro no existe en el contexto de evaluación previo se une a dicho contexto. El resultado, por tanto, mostrará un resultado en blanco para aquellos clientes femeninos.

Realiza la expresión SUM (Datos [Libros Comprados]) en el nuevo contexto con el resultado de 5 libros comprados por Andrés.

Hagamos el mismo análisis, pero en este caso para el cliente “Mar”

1. Observemos cual es contexto de evaluación inicial.
2. A continuación, CALCULATE sustituye el contexto existente por el que lleva incorporado en el filtro género. El contexto resultante es que para el cliente Mar no existe género Masculino por lo que.
3. No hay resultado posible salvo un resultado en blanco.

Finalmente, veamos qué ocurre si incorporamos de nuevo el campo género a las filas de la tabla dinámica e intenta discernir qué ocurre en cada uno de los resultados en función del contexto en el que se evalúa la expresión.

Analicemos por último la siguiente expresión:

Numero de libros comprados por Mar y Mujeres:=

CALCULATE (

SUM (Datos[Libros Comprados] );

Datos[Género] = "F";

Datos[Nombre] = "Mar"

)

Que ofrece el siguiente resultado:

Los filtros de género y nombre aparecen en el contexto de evaluación inicial pero también están dentro de la expresión de CALCULATE por lo que en este caso CALCULATE anula por completo el contexto de evaluación en todos los aspectos mostrados por la tabla dinámica e impone el suyo: “Libros comprados por el género femenino y el cliente Mar”. En ese orden.

La función CALCULATE, al igual que todas las demás funciones y expresiones que diseñemos puede ser usada tanto en medidas, como para en columnas calculadas que añadiremos a nuestro modelo de datos. Pero hay que tener en cuenta que dependiendo de dónde las usemos se comportarán de una forma diferente.

En el caso de que usemos CALCULATE en una expresión de columna calculada en nuestro modelo de datos la expresión integrará implícitamente el contexto de fila existente en la evaluación de la expresión.

Pongamos un ejemplo:

Supongamos las siguientes expresiones. Recuerde que cuando presentemos una medida la representaremos como [nombre de la medida]:=expresión y en caso de una columna calculada la representaremos como [nombre de la columna calculada]=expresión

Una medida que sumará el número de libros comprados por todos nuestros clientes

[Suma de Libros Comprados]: =SUM ([Libros Comprados])

Por otro lado, añadiremos una columna calculada en nuestra tabla.

[Columna calculada de la Suma de Libros Comprados] =SUM ([Libros Comprados])

La expresión [Suma de Libros Comprados] devuelve la suma de los libros comprados en el contexto de filtro actual siendo este contexto el de toda la tabla sin restricciones por lo que, en una medida sin restricción alguna de filtro, el resultado será la suma de toda la columna.

Qué ocurre si ponemos la misma expresión en una columna calculada, pues que el comportamiento de la expresión será el mismo, primero evaluará el contexto de filtro previo (no existe contexto de filtro ya que no hay tabla dinámica, segmentadores u otro tipo de filtros) y después realizará el cálculo que se le solicita, la suma de todos los libros comprados, es decir, la suma de toda la columna ‘Libros Comprados’.

Modifiquemos ahora la columna calculada anidando la expresión original dentro de una función CALCULATE sin filtros de la siguiente forma:

CALCULATE evalúa el contexto de filtro existente, ninguno en este caso, después evalúa el contexto de cada fila para la que realiza el cálculo incorporándolo a su expresión y finalmente realiza la operación en el contexto resultante.

Este comportamiento de CALCULATE es muy útil para preparar la información en las tablas y necesitaremos para determinados cálculos.

Llegados a este punto sería interesante ampliar el proceso seguido por la función CALCULATE.

1. Analiza el contexto de valuación en el que se encuentra la expresión.
2. Si el filtro que incorpora CALCULATE ya está en la tabla dinámica lo anula y si no lo está, se une a él como nuevo filtro en el contexto de evaluación.
3. En el caso de columnas calculadas incorpora implícitamente como filtro el contexto de fila existente.
4. Ejecuta el cálculo de la expresión.