A falta que Microsoft implemente KPI en Power BI o mejore las posibilidades del formato condicional, expongo aquí un par de trucos para representar con imágenes los valores de nuestros informes

Lo que quiero es representar por ejemplo con unas flechas los valores positivos o negativos, a falta de los semáforos de colores del formato condicional de Excel.

Parto de la plantilla A3ERP ventas totales detalladas que puede descargar

En este ejemplo vamos a usar algunas opciones que están en modo preliminar, por tanto asegúrese que la tiene activas.

Vamos a Archivo, Opciones y configuración, Opciones, y seleccionamos Características de versión preliminar

Asegurándonos que tenemos activadas Nuevo objeto visual matriz y Medidas rápidas

Creando la matriz de datos

Añadimos un objeto Vista previa de la matriz

Con

Y quitamos de la jerarquía de fecha el trimestre, mes y día, para que nos quede una matriz así

Donde vemos las ventas de cada año.

Mejoremos la presentación

Con formato, estilo de la matriz, filas llamativas con encabezado en negrita

Para aumentar el tamaño de los datos vamos a Formato, Cuadricula, Tamaño del texto

Quitemos la columna de totales con Formato, Subtotales, Columna de totales, la desactivamos

Ahora tiene mejor aspecto

Medida rápida: Incremento año a año

Queremos ver el incremento de un año respecto al anterior, para ello usaremos las nuevas medidas rápidas

Sobre el campo EUR pulsamos con el botón de la derecha y seleccionamos medidas rápidas

Y como calculo seleccionamos Cambio de año a año

Esto crea una nueva medida, cuya fórmula podemos ver en pantalla

Y nos lo añade a la matriz

Visualización de valores negativos

Ahora vamos a crear una nueva medida con la formula

YoY_KPI_1 = IF([YoY% de EUR]<0;UNICHAR(9660);””)

Lo que interpretamos como

Si él % de aumento anual es <0, muestra el carácter UNICODE 9660, que en este caso es un triángulo con el vértice hacia abajo

Si añadimos la medida a la matriz, veremos

Los valores negativos tienen un triángulo descendente.

Visualización de valores negativos y positivos

Si queremos una imagen para positivos y negativos la medida implica un IF anidado

YoY_KPI_2 = IF([YoY% de EUR]<0;UNICHAR(9660);IF([YoY% de EUR]>0;UNICHAR(9650);””) )

O sea si es positivo, hacemos otro IF y visualizamos el carácter UNICODE 9650

Añadimos la medida a la matriz y hacemos más estrechas las columnas del año 2015 que no tienen valores

Los incrementos negativos tienen y los positivos

Imagen con estrellas

Vamos a crear una nueva medida que representa con estrellas el incremento

YoY_KPI_3 = IF([YoY% de EUR]>0;REPT(UNICHAR(9733);INT([YoY% de EUR]*10));REPT(UNICHAR(9734);INT([YoY% de EUR]*-10)))

Aquí estamos usando la función REPT que repite un carácter un determinado número de veces

Si es positivo tomamos la parte entera del incremento * 10

Si es negativo la parte entera del incremento * -10

La ponemos en la matriz y tenemos una imagen del incremento

Cuantas más estrellas oscuras, mayor él es el incremento, cuantas más estrellas blancas mayor es la disminución de ventas.

Ideas extraídas y adaptadas de

Create KPIs in Power BI – Power BI Tips & Tricks #41

The DAX Unichar() Function

Y un lugar donde buscar los caracteres Unicode

Unicode lookup

En la actualización de Power Bi de Abril 2017 han añadido una nueva funcionalidad en editor de consultas que nos puede ahorrar mucho trabajo.

Se trata de proporcionar un ejemplo de lo que queremos, y el Power Bi lo interpreta y nos crea la columna con la formula o conversión necesaria

Para verlo partimos de la plantilla XLS Contabilidad que podemos descargar

Agregar columnas a partir de los ejemplos

Si vamos al editor de consultas

Veremos que tiene dos tablas: apuntes y cuentas

Por supuesto en la tabla apuntes las cuentas están con todos sus dígitos

Pero para hacer resúmenes nos interesa obtener los grupos o niveles contables, o sea las cuentas de 1 digito, de 2 y de 3

Para ello seleccionamos la columna cuentas y pulsamos en Agregar columna, Columna a partir de los ejemplos

Se nos muestra una nueva columna para que indiquemos un ejemplo de lo que quereos conseguir: en este caso pongo la primera cifra de la cuenta del primer apunte

Automáticamente el sistema me muestra lo que pondrá para las filas, y como parece correcto pulsamos aceptar

El sistema ha creado una función en el leguaje de consultas

Pero eso no nos afecta, pues el resultado es el que nos interesa

Cambiamos el título a la columna por Nivel 1

Y pulamos en el filtro para verificar si es correcto

Y haremos lo mismo para tener las cuentas de nivel 2: Agregar columna a partir de los ejemplos

Al crearla y verificarlo con el filtro vemos que no es correcto

Esto nos demuestra que siempre debemos comprobar los resultados de los mecanismos automáticos

Eliminemos el paso aplicado, pulsando en la X del paso

Sugiero ordenar los apuntes por cuenta descendente

Y volver a agregar columna

Si ponemos solo las cifras de la primera cuenta, se ve que las demás no están bien

Démosle más pistas al sistema poniendo un valor para la segunda cuenta

Cambiamos el nombre a Nivel 2 y verificamos con el filtro

Observe que al hacer el nivel 3 y poner 3 primeras cifras, se muestra un valor que no queremos para la segunda fila

Por lo tanto debemos corregir este valor en la segunda fila

Ahora ya podemos aceptar y verificar el resultado

Por tanto esta muy ben tener un asistente, pero debemos fijarnos siempre en lo que nos propone

Este sistema es una gran ayuda para las transformaciones de datos.

Segundo ejemplo: transformar mayúsculas

Vamos a aplicar una en el plan de cuentas: sencillamente queremos que los nombres de las cuenta no estén todo en mayúsculas

Seleccionamos la tabla cuenta, agregamos columna a partir de los ejemplos, y escribimos el texto como lo queremos

El sistema lo detecta y nos propone el formato que nos interesa

Cambiamos el título de la columna

Relaciones de tablas

Ahora, si cerramos y aplicamos y vamos al panel de relaciones veremos que ya se ha creado la relación entre la cuenta de asientos y la cuenta de cuentas

Pero en nuestro caso también necesitamos el nombre de la cuenta de nivel 2 y 3

Podemos arrastrar Nivel 2 hacia Cuenta, y veremos que se crea una relación no activa, dibujada en rayas

Por tanto esta relación no nos sirve: no se puede relacionar una tabla con otra más de una vez

Necesitamos una tabla con las cuentas de nivel 2 y otra con las cuentas de nivel 3

Eliminemos esta relación y vamos al editor de consultas

En la tabla cuentas necesitamos una columna que nos de él nivel: el número de dígitos de cada cuenta

Vamos a probar, seleccionamos la columna cuenta y pulsamos Agregar columna a partir de los ejemplos, y en la columna nueva ponemos, 2, los dígitos de la primera cuenta, en las demás no ocurre nada

Sigamos con las siguientes a ver qué ocurre

El sistema no detecta nada, así pues esta técnica en este caso no nos sirve. Cancelemos

Seleccionemos la columna Cuenta, y pulsamos en Agregar columna, Extraer, Largo

Esto nos crea una nueva columna con la longitud de cada cuenta, que podemos cambiar el título por Nivel

Tabla referenciada a otra

En la tabla cuentas, con el botón de la derecha hacemos clic en Referencia

Esto nos crea una nueva tabla,

Pero no con los valores copiados, no es un duplicado, sino que esta referenciada a la prim4era. Cualquier cambio que hagamos en la de Cuentas afectara a la nueva.

Cambiemos el nombre por Cuentas 2 y en Nivel filtremos por 2

Ya tenemos una tabla con las cuentas de Nivel 2

Y cambiemos el título de Cuenta por Cuenta 2

Seguiremos el mismo sistema para las de nivel 3

La tabla cuentas la hacemos una Referencia

Cambiamos el título a Cuentas 3

Y Filtramos el nivel a 3 dígitos

Cerramos y aplicamos y en el cuadro de relaciones ya podemos crear la relación entre los niveles y sus tablas de cuentas

Verificación final

Vamos a verificarlo con una tabla creando una tabla con

Lo que nos muestra un mapa de cuentas jerárquico

Por lo tanto el asistente para agregar columnas es una gran herramienta…siempre que comprobemos el resultado que nos da.

En la actualización de abril 2017, Microsoft ha lanzado un sistema de cálculo de medidas rápidas en Power Bi, que facilitará mucho el trabajo a los usuarios. Y además como te crea las medidas con DAX podemos ir aprendiendo viendo lo que el sistema ha diseñado

Vamos a ver con ejemplos los distintos tipos de medidas rápidas, que seguro irán aumentando en futuras versiones

Hay cinco grupos de cálculos

• Agregados de la categoría
• Filtros y líneas base
• Inteligencia de tiempo
• Total acumulado
• Operaciones matemáticas

Y cada grupo tiene sus propias medidas

Partimos de la plantilla A3ERP Ventas totales detallada base que puede descargar.

Total acumulado

Queremos ver cómo evolucionan las ventas a lo largo del año, acumulando mes a mes

Creamos una matriz y ponemos

Usamos el Date para tener la jerarquía de fechas, pero como solo nos interesan los años eliminados los otros campos

Esto nos da una matriz con

Ahora queremos el acumulado mes a mes, a lo largo del año.

Pulsamos sobre la medida EUR y con el botón de la derecha escogemos Medidas rápidas

Se nos muestra la pantalla de cálculo y escogemos Total acumulado

Se nos muestra la pantalla para definir que queremos acumular

En este caso es fácil, nos va bien lo que nos propone EUR acumulados por Mes en orden Ascendente

Al aceptar, se nos muestra la medida creada

Vamos a acortar el nombre, cambiamos Total acumulado de EUR en Mes por EUR acumulado

Ahora nuestra Matriz tiene las ventas mes a mes y el acumulado a lo largo del año

Inteligencia de tiempo

En varias de mis entradas de blog he explicado las funciones de inteligencia de tiempo. Y siempre he recalcado que debemos tener una tabla calendario con rangos continuos de fecha para poder usarlas. Y he expuesto diferentes maneras de crear dichos calendarios.

Ahora vamos a ver como utilizamos las medidas rápidas de inteligencia de tiempo.

Aprovechando la matriz anterior nos iría bien tener la diferencia de un año respecto al anterior

Duplicamos la página y en la nueva matriz en la medida ERU acumulado, con el botón de la derecha usamos Medida rápida

Seleccionamos el cálculo: cambio año a año

Lo que nos crea una formula DAX que vemos en pantalla

Acortamos el título a YoY% EUR. Y ahora en la matriz tenemos el acumulado mes a mes y la diferencia de dicho acumulado respecto al año anterior

Vamos a usar esta misma medida para ver la información de otros dos modos

Incrementos de ventas para clientes año a año

En una nueva página, creamos una nueva matriz y ponemos

Estoy usando el Año de la tabla calendario para demostrar que no funciona. La matriz nos da 0,00% en el YoY%

Cambiemos el año por el date y eliminamos día, mes y trimestre.

Ahora si tenemos las ventas por cliente y su aumento año a año.

Como son medidas DAX que crea el sistema podemos usarlas en diferentes informes, igual que las medidas que creamos nosotros mismos

Vamos a crear una tabla más simple con

Recuerde que usamos el Date con jerarquía, quedándonos solo con el año, Esto nos da una tabla de aumentos anuales

Acumulados en el año, en el trimestre y en el mes

Vamos a usar medidas rápidas para ver la evolución de las ventas en distintos período de tiempo

Creamos una tabla con

Y obtenemos el detalle de ventas día a día

Notemos que solos se muestran los días con ventas

En la medida EUR con el botón de la derecha usamos Medidas rápidas y elegimos Total mensual hasta la fecha

Y aceptamos los valores por defecto que se muestran EUR y Date

Se ha creado una medida cuya fórmula DAX vemos en la parte superior

Y en la tabla aparecen todos los días del mes y el acumulado dentro del mes

Observe que al cambiar a febrero vuelve a empezar a contar: es el acumulado dentro del mes

Vamos a crear también las medidas Total trimestral hasta la fecha, y Total anual hasta la fecha

Y si quitamos el día en la tabla

Veremos que tanto el EUR como el MTD en EUR tienen el mismo valor, pues no hay detalle de días

El QTD de EUR va acumulando dentro del trimestre, y cuando cambia de trimestre vuelve a empezar a sumar

Y el YTD de EUR acumula dentro del año, y empieza a sumar de nuevo al cambiar el año

Operaciones matemáticas

Queremos obtener una tabla que nos del precio medio de venta de los distintos artículos

Añadimos un visual tabla con

Y esto nos muestra los EUR y el total de unidades vendidas. Por tanto necesitamos dividir los EUR por las unidades

En el EUR pulsamos en Medidas rápidas, y seleccionamos División

Y ya nos propone lo que nos interesa

Creando una medida cuyo nombre podemos cambiar

Y en la tabla tenemos el precio medio para cada artículo

Filtros y Lineas base

Ahora que dicen que hemos pasado la crisis quizás nos interese hacer comparativas con años anteriores…muy anteriores

Vamos a comparar las ventas de cada año, con las de un año base. En este caso como solo tenemos 2015, 16 y 17 compramos con 2015

Creamos una matriz con

Lo que nos da las ventas para cada cliente y año

Queremos comprara las del 2016 con el 2015 y las del 2017 también con 2015

Usamos la medida rápida de Filtros y líneas base de Diferencia respecto a la línea base

El sistema nos propone

Pero no nos interesa comparar con otro cliente, sino con un año que fijemos: el 2015

De la tabla Calendario arrastramos el Date sobre cliente

En el Date, desplegamos e indicamos Año

Y en Seleccione un valor desplegamos e indicamos 2015

Al aceptar se crea una nueva medida con la formula DAX

Acortamos el nombre con Dif s/2015

Y en la tabla ya se muestra la diferencia de todos los años respecto al 2015

Podemos obtener también la Medida rápida: Diferencia de % respecto a línea base

Si acortamos el nombre de la medida como %Dif s/2015 en la tabla obtenemos

Las ventas de cada año comparadas con el año base: 2015

En este tutorial no hemos visto los Agregados de la categoría…pues todavía no se me ocurre un ejemplo donde este cálculo me sea útil.

Sabemos que con los segmentadores ponemos poner filtros para los periodos de tiempo que nos interesen: por ejemplo por meses o por años.

Yo quiero facilitar más el trabajo al usuario y permitirle elegir el filtro para mostrar información del Año actual, o del Mes actual, directamente, como en esta pantalla:

Parto de la plantilla A3ERP Ventas totales detallada base que puede descargar.

La tabla de datos

Creamos una tabla de datos con

Y si le damos un poco de forma tenemos

Las ventas para cada año y mes

Los segmentadores clásicos

Añadimos unos segmentadores por año

Y otro por mes

Y evidentemente podemos hacer clic en el año en que estamos y el mes en que estamos y veremos su información

Año actual

Acedemos a Datos y en la tabla calendario

Vamos a añadir una nueva columna

Qué Año = IF(

Calendario[Date].[Año]=YEAR(TODAY());

“Año actual”;

“Otros Años”)

Estamos usando una función SI donde indicamos que año de la fila (DATE) sea igual al AÑO de HOY

Si se cumple ponemos Año actual

Y si NO se cumple ponemos Otros años

Y añadimos este segmentador

Ahora si filtramos por Año actual se muestran solo los valores de este año

Mes actual

Para el mes actual seguiremos el mismo procedimiento, ir a la tabla calendario, añadir una nueva columna con

Que Mes = IF(

Calendario[Date].[Año]=YEAR(TODAY()) && Calendario[Date].[NroMes]=MONTH(TODAY());

“Mes actual”;

“Otros meses”)

Usamos el mismo sistema anterior, primero buscamos si el año de la fecha es el actual y luego si el mes de la fecha es el de hoy

Añadimos un segmentador con

Y en el momento de filtrar por Mes actual, vemos solo la información correcta

En mi ejemplo Abril de 2017. Observe que los otros segmentadores se han ajustado automáticamente.

Por supuesto esto es dinámico, si abrimos en mayo 2017 y pulsamos Mes actual veremos las ventas de ese periodo

Un interesante truco usando función IF y trabajando con la tabla calendario.

Las medidas que usamos en nuestros informes, en principio solo se pueden mostrar en las columnas, sea a través de una tabla o de una matriz.

Pero a veces nos puede interesar mostrarlas en filas para hacer más legible el informe.

Este es un ejemplo: en la parte superior tenemos la típica tabla que muestra las medidas en columnas, y en la parte inferior mostramos lo mismo pero las medidas, los cálculos en filas:

La parte inferior tiene más sentido, dado el tipo de información que presentamos: ventas, costes, beneficios y margen.

Parto de la plantilla A3ERP Ventas totales detallada base que puede descargar.

Medidas básicas

El informe base ya trae una medida EUR, la suma de las ventas, vamos a cambiarle el nombre, seleccionamos EUR y cambiamos EUR por Venta

Venta = sum(Ventas[Neto])

Para calcular el coste creamos la medida:

Coste = SUMX(Ventas;Ventas[Precio Coste]*Ventas[Unidades])

Para el beneficio la medida:

Beneficio = [Venta]-[Coste]

Y el margen sobre venta con la medida:

% Margen s/venta = [Beneficio]/[Venta]

No olvidemos darle formato porcentaje

Podemos ya construir la primera matriz con

Y si le damos un poco de formato obtenemos

La información donde las filas son los representantes y las columnas las medidas.

Dato el tipo de información que presentamos quiero ponerlo de otra forma: transponer filas por columnas.

Para ello deberemos usar algunos trucos

Crear tabla de medidas

Lo primero será crear una tabla con los nombres de las medidas. Vamos a Inicio, Especificar datos, y creamos una tabla como la muestra

Los nombres que pongamos deben ser fáciles de recordar y mejor que coincidan con los nombres de las medidas.

Sugiero añadir también la columna Orden, donde podemos indicar en qué orden queremos que se muestren las medidas en la tabla, pues en caso contrario lo harán alfabéticamente.

Guardamos la tabla como Medidas

Medida a mostrar

Ahora vamos a crear una nueva medida con

MedidaAMostrar = IF(

HASONEVALUE(Medidas[Medida]);

SWITCH(

VALUES(Medidas[Medida]);

“Venta”;Ventas[Venta];”Coste”;[Coste];”Beneficio”;[Beneficio];”% margen s/venta”;[% Margen s/venta]))

Lo que interpretamos como

Si

Hay un valor en Medida

Haz un SWITH, un si anidado según lo que devuelva VALUES(Medidas[Medida]): el nombre de la medida en la tabla

Y hemos puesto pares de valores: el nombre de la medida, y la medida en si misma

Matriz de filas con medidas

Ahora podemos construir la matriz con

Le ajustamos un poco la estética y nos da como resultado

Vamos a corregir dos cosas: primero el % no sale bien, hay que multiplicarlo por 100 y segundo el orden de los campos: las ventas no debe ser lo último

Para corregir el % editamos la medida y multiplicamos el % por 100

MedidaAMostrar = IF(HASONEVALUE(Medidas[Medida]);SWITCH(VALUES(Medidas[Medida]);”Venta”;Ventas[Venta];”Coste”;[Coste];”Beneficio”;[Beneficio];”% margen s/venta”;[% Margen s/venta]*100))

Y para caminar el orden de los datos vamos a Datos, nos situamos en la tabla Medidas

Y usamos el Ordenar por columna, ordenando por Orden

Ahora la tabla ya tiene otra lógica

Para cada representante vemos sus ventas, coste, beneficio y % margen s/venta

Así pues hemos encontrado un truco para cambiar filas por columnas, y mostrar las medidas en las filas.

La idea la he extraído de Power BI Selecciones Edición 02 – 2da Parte

Una de las grandes ventajas de Power Bi es que en un solo panel, una sola pantalla podemos representar la información con varias tablas o gráficos.

Pero a veces puede haber tanta información que es conveniente guiar al usuario y decirle lo que debe hacer para poder ver la información.

Este es el planteamiento de esta entrada: solo se mostraran datos si el usuario ha filtrado por algún criterio.

Parto de la plantilla A3ERP Ventas totales detallada base que puede descargar.

Presentación básica

En la parte superior tenemos dos segmentadores: año y representante

Y en la inferior tres modos de ver la información: por meses, por familias y una matriz por cliente, familia y artículo.

El grafico de barras para meses es:

El grafico de círculo para familias es:

Y la matriz de clientes y artículos es

Medida con filtro

Lo que quiero es que hasta que el usuario no haya aplicado un criterio, no se muestre nada.

Para ello vamos a crear una nueva medida y usar las funciones NOT y HASONEFILTER. Creamos una medida y ponemos

EUR con filtro = IF(NOT(HASONEFILTER(Calendario[Año]));0;[EUR])

Estamos diciendo

SI, NO hay filtro en el campo año, pon 0, en caso contrario por EUR

Ahora basta ir a nuestros visuales y cambiar la medida EUR con la nueva medid EUR con filtro

Por ejemplo en la matriz pondremos, y lo mismo hacemos en los otros dos visuales, cambiamos la medida EUR por EUR con filtro

Y si no tenemos ningún año seleccionado, no se muestra nada en ningún visual

No hay ningún año seleccionado, pues no mostramos datos en los gráficos y la tabla muestra 0

Seleccionamos un año, por ejemplo 2014 y se muestran sus valores.

Vamos un poco más allá y queremos que tampoco se muestren valores sino ha se ha filtrado por representante.

Por tanto estamos diciendo, si no hay filtro en año O no hay filtro en representante, no muestres nada.

Para ello debemos modificar la medida, la seleccionamos y añadimos lo que marco en negrita

EUR con filtro = IF(NOT(HASONEFILTER(Calendario[Año])) || NOT(HASONEFILTER(Ventas[Representante]));0;[EUR])

El símbolo | puesto dos veces || equivale a decir O

Ahora como tenemos un año filtrado pero no hemos filtrado por representante, NO se muestran valores.

Basta filtrar por un representante y SI veremos valores

Si queremos que baste con poner un filtro, o sea o un año o un representante cambiaríamos la medida con

EUR con filtro = IF(NOT(HASONEFILTER(Calendario[Año])) && NOT(HASONEFILTER(Ventas[Representante]));0;[EUR])

En lugar de || ponemos &&, o sea en lugar de un O aplicamos un Y

Añadir un titulo

Dado que si abrimos el grafico y no hay nada seleccionado el y usuario no sabrá que hacer, podemos poner un título que le dé información.

Por ejemplo Seleccione un año y un representante para ver los datos

Para ello creamos una nueva medida

Titulo = IF(NOT(HASONEFILTER(Calendario[Año])) || NOT(HASONEFILTER(Ventas[Representante]));”Seleccione un AÑO y un REPRESENTANTE para ver los datos”;[EUR])

Y si la ponemos en una Tarjeta con

Ahora al abrir el panel el usuario tiene instrucciones

Quedando el panel con instrucciones

Si seleccionamos un año se muestran datos. Pero el titulo nos pone el total de ventas, pues hemos dejado la medida EUR.

Vamos a perfeccionarlo haciendo que el título muestre lo que hemos seleccionado.

Modificamos la medida título y sustituimos el EUR por lo que he puesto en negrita

Titulo = IF(NOT(HASONEFILTER(Calendario[Año])) || NOT(HASONEFILTER(Ventas[Representante]));”Seleccione un AÑO y un REPRESENTANTE para ver los datos”;“Año:” & VALUES(Calendario[Año]) & ” Representante:” & VALUES(Ventas[Representante]))

Estamos concatenando un texto “Año;” con el valor seleccionado a través de la función VALUES que nos devuelve el valor de una tabla, como esta filtrada solo nos devuelve el año que tenemos seleccionado.

Ahora cuando hemos filtrado el titulo nos da más información

A si pues en esta entrada hemos introducido algunas funciones interesantes NOT, HASNONEFILTER y VALUES

La idea la he extraído de Power BI (desktop) – Filtro de Relatório / Medidas que guiam o usuário pelo painel