El problema

Es frecuente tener que construir informes en los que se presente información con muchas columnas. En Power BI ¿podríamos alternar valores período-acumulado en una misma tabla?

Supongamos una tabla con la cuenta de pérdidas y ganancias de una empresa con varias delegaciones, para cada una de las cuales se requiere ver los valores del periodo elegido, comparados con los del año anterior, mostrando variaciones, las mismas columnas con los valores acumulados hasta el periodo, el presupuesto y, tampoco sería raro, un Forecast y variación respecto a los mismos.

Probablemente el resultado final será una tabla con un scroll horizontal que siempre resulta poco manejable y ofrece una limitada visión global de las delegaciones. Quizá, una tabla similar a la siguiente:

Habrá usuarios demanden este estilo de informe “sábana”. Pero habrá otros que agradecerían una visión más enfocada y no tener que navegar de derecha a izquierda continuamente. Podríamos rediseñar el informe y mostrarlo de otro modo, permitiendo alternar los valores a mostrar fácilmente, algo así:

En Power BI tenemos dos alternativas: La fácil, que es duplicar los informes y, vía botones de navegación, ir pasando de uno a otro, y la de usar DAX para proporcionar la funcionalidad descrita.

La primera es muy limitada y quizá fuese la adecuada para algún un caso puntual en un proyecto.
La que vamos a ver es algo más compleja, requiere diseñar las medidas, algo más de desarrollo, pero va a permitir, además de diseñar el report en cuestión, reutilizar la funcionalidad en otros informes y visuals del proyecto.

El modelo de datos utilizado

En este ejemplo partiremos de un modelo muy simple, sin agrupaciones, en el que sólo se utiliza una tabla de hechos que contiene todos los datos necesarios.

Aunque sea un punto de partida muy básico, las explicaciones que voy a dar son extrapolables a modelos de datos más complejos.

La tabla tiene las siguientes columnas:

Tabla de hechos utilizada

Tabla de hechos utilizada

Para aprovechar la funcionalidad Time Intelligence vamos a crear una tabla de fechas, por años y meses, con la que relacionaremos usando los campos de fecha. En este post puedes ver un ejemplo completo de creación dinámica de tablas de fecha con DAX.

Finalmente, para ofrecer el filtro Periodo-Acumulado crearemos una tabla manual, en el datawarehouse, en Excel, dentro del Query Editor de PowerBI (opción elegida)… donde quieras. En todo caso, esta es su estructura y su contenido:

Power BI-> Query Editor -> Enter Data

Agrégala al modelo de datos de PowerBI y no la relaciones con ninguna otra tabla del modelo.

Las relaciones en PowerBI quedarían así:

Las medidas a crear con DAX

Nuestro informe podrá filtrarse por año, mes, y va contener una tabla en la que las filas son los conceptos y las columnas los departamentos, y por cada departamento tendremos las siguientes medidas:

Nombre Medida Descripción DAX
Sum Importe El importe del mes elegido
Sum Importe = sum(pyg_centros[Importe])
Sum Importe PY El importe del mismo periodo del año anterior
Sum Importe PY = Calculate([Sum Importe]; sameperiodlastyear(fechas[Fecha]))
Sum Importe YTD Acumulado anual de importe hasta el mes elegido
Sum Importe YTD = totalytd([Sum Importe]; fechas[Fecha])
Sum Importe PY YTD Acumulado anual de importe hasta el mes elegido del año anterior
Sum Importe PY YTD = totalytd([Sum Importe PY]; fechas[Fecha])

Nuestro modelo de datos será similar al siguiente:

Si confeccionamos la tabla con estas medidas y las premisas iniciales, ya observamos que, aunque el volumen de datos y variables es reducido, la tabla es poco manejable:

Si a esto sumamos que además queremos analizar las correspondientes variaciones en términos absolutos y porcentuales entre ambos pares de medidas, periodo-acumulado, la legibilidad del informe ya se puede ver seriamente comprometida.

Programando la alternancia: Medidas DAX adicionales

Para arreglarlo, añadiremos a nuestro report un filtro que permitirá alternar los valores de la tabla. También vamos a crear dos nuevas medidas que van a mostrar los valores de las medidas anteriores según la vista elegida en el filtro “Vista”:

Nombre Medida Descripción DAX
Valor Valor condicionado: Periodo/Acumulado
Valor = if( VALUES(periodo[vista])="Acumulado";[Sum Importe YTD];[Sum Importe])
Valor PY Valor año anterior condicionado: Periodo/Acumulado
Valor PY = if( VALUES(periodo[vista])="Acumulado";[Sum Importe PY YTD];[Sum Importe PY])

Si reconstruimos la tabla anterior con estas nuevas medidas veremos que la gestión de la misma se ha simplificado significativamente para los usuarios:

Y ya podrán alternar entre una vista y otra cambiando el filtro. Además, estas medidas van a poderse utilizar en cualquier otro Visual o pantalla que se requiera.

NOTA: Si no hay elegido valor en el filtro “Vista” la tabla mostrará un error:

Para estos casos podríamos arreglar las fórmulas DAX para que muestren un valor por defecto o añadir un filtro de página que asignase un valor predeterminado a la vista: Ambas opciones son válidas, aunque lógicamente aconsejo la primera.

Medidas DAX para la variación interanual

Falta crear las medidas de variación absoluta y porcentual entre periodos. Éstas las haremos sobre las medidas “Valor” y “Valor PY”:

Nombre Medida Descripción DAX
Variación Diferencia absoluta interanual (periodo-periodo anterior)
Variación = [Valor]-[Valor PY]
Variación % Variación anterior en términos porcentuales respecto a periodo Anterior
Variación % = divide([Valor]-[Valor PY];[Valor PY])

Las medidas finales del modelo serán:

Y tras añadirlas a la tabla, el resultado final del informe será algo similar al siguiente, para la vista periodo:

Y acumulado:

Conclusión

El caso que se ha planteado no es un modelo exento de limitaciones y en proyectos de mayor entidad, con más tablas y medidas que se desee se comporten igual que se ha descrito, puede añadir una gran complejidad al desarrollo. Pero ante determinadas necesidades ofrece una solución de presentación de datos eficiente y elegante, y ofrece un punto de vista diferente que espero te sea útil en tus próximos desarrollos.

  1. Santiago,
    no entendí bien esta parte de la formula Sum Importe PY = Calculate((pyg_centros[Periodo]); sameperiodlastyear(fechas[Fecha]))
    donde dice pyg_centros[Periodo], la columna Periodo no existe en la tabla pyg_centros, al menos no la veo en tu modelo, porque la colocas ahi, intento emular el mismo ejemplo pero me la herramienta me dice que no existe, me podrias explicar, muy agradecido.

    • Hola Paul, he revisado las fórmulas y he corregido un error en las mismas (copié al post desde un modelo con nombres diferentes). Disculpa y muchas gracias por la observación,.

      • Gracia Santiago, ya quedó y me fue de mucha ayuda, Justamente estoy trabajando un Dashboard Financiero en Power BI, tienes algunos otros ejemplos que me podrian ayudar. gracias de antemano.

  2. Genial post Santiago. Muchas gracias.

    Voy a replicar este desarrollo en mi proyecto actual. No obstante, tengo una duda antes de comenzar. ¿Este informe permitiría acumular, por ejemplo, costes desde el origen del proyecto -enero 2015- hasta el mes de análisis -septiembre 2019-?

    Es decir, necesitaría poder comparar el coste del presupuesto vs real incurrido. Pero quizás si en el filtro dejo sin seleccionar desde octubre a diciembre, entiendo que se elimina el coste de esos meses de todos los años del análisis (es decir, del 2015-16-17-18 y 19). Sin embargo, solo querría excluir los meses de oct – dic pero solo del 2019.

    Muchas gracias de antemano
    Saludos

    • Hola Daniel, si lo he entendido bien, en la pantalla podrías poner un filtro fecha (desde hasta) para el periodo y crear una dax que hiciera el acumulado total hasta el “hasta”:
      Total Acum. Hasta fecha =
      CALCULATE (
      SUM ( 'Tabla'[Valor] ),
      FILTER (
      ALL ( 'Tabla' ),
      'Tabla'[Fecha] <= MAX ( 'Tabla'[Fecha] )
      )
      )

      Este calculo sólo contempla el límite superior y debería acumular desde que haya datos (ALL)

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *