Vamos a ver cómo construir una tabla y gráfico que represente un Pareto o diagrama ABC dinámico con Power BI.
Los datos del ejemplo
Se ha utilizado la base de datos SQL Server “WideWorldImportersDW” de Microsoft. Puedes descargarla de aquí.
Para el caso sólo vamos a usar las tablas indicadas en siguiente diagrama:
Y lo que pretendemos es hacer un ABC de los productos vendidos, que están en la tabla “Dimension Stock Item”, y que marque en diferente color aquellos que acumulen un porcentaje de las ventas que ajustaremos dinámicamente con un Slicer.
Preparación
Además de los datos a analizar, necesitaremos una tabla que sirva los valores a mostrar en el Slicer.
Esta tabla, que llamaremos “Umbral Color”, es muy sencilla de crear y contendrá enumeración de valores del 1 a 100 que se crea con una fórmula DAX:
Umbral Color = GENERATESERIES(1;100;1)
Añadiremos un slicer y arrastremos la columna, que previamente habremos renombrado como “% de Ventas”. Si solo nos interesa el umbral superior, dejamos el comportamiento del filtro para que solo permita ajustar el nivel máximo, pero para un ABC necesitaremos el comportamiento por defecto, que es “between”:
Las medidas necesarias
Este gráfico requiere calcular un acumulado de la suma “Ventas” de los elementos de la dimensión para la que se va a hacer la representación, en este caso los nombres de los artículos, aunque también es típico hacerlo para clientes. Este acumulado no está basado en una evolución temporal, sino en una ordenación o ranking de los elementos de esa dimensión.
También necesitamos un total de las ventas de todos los productos, por que necesitamos calcular el porcentaje del anterior acumulado sobre este total.
Finalmente, queremos aplicar colores dinámicamente, según el porcentaje seleccionado en el umbral. Este color vendrá dado como resultado de otra fórmula DAX.
En resumen, (a efectos didácticos) crearemos estas medidas (en la práctica podríamos resolverlo con 3):
Total Ventas va ser la suma de la venta sin tasas, y sobre la que vamos a operar en el resto de fórmulas:
Total Ventas = sum('Fact Sale'[Total Excluding Tax])
Necesitamos calcular el acumulado de esta medidas para los productos:
Acumulado =
VAR Medida=[Total ventas]
RETURN
SUMX(
FILTER (
SUMMARIZE (ALLSELECTED ('Fact Sale') ; 'Dimension Stock Item'[Stock Item] ; "VentaAcum"; [Total ventas] ) ;
[VentaAcum] >= Medida ) ;[VentaAcum] )
La función SUMMARIZE ejecuta una consulta de agrupación que devuelve 2 columnas:
- ejecuta la consulta contra una tabla (primer parámetro),
- agrupando por un campo (segundo parámetro),
- sumando una medida que tendrá un alias (cuarto y tercer parámetros).
Así, consultamos la tabla “Fact Sale”, pero sólo los datos que obedezcan a la selección actual, por eso el ALLSELECTED (‘Fact Sale’). Agruparemos por el nombre del producto y el “Total ventas” vendrá bajo el alias “VentaAcum”.
Después, sobre esta consulta, hará una suma de los valores resultantes de filtrar aquellos valores con venta mayor o igual a las del producto actual. Ten en cuenta que entran en juego los contextos de línea para hacer cálculo.
Necesitamos el total de las ventas para la selección hecha en el report:
Total All Selected = CALCULATE([Total Ventas]; ALLSELECTED ('Fact Sale'))
Ya dispondremos de todas las medidas necesarias para calcular el porcentaje del acumulado:
% Acumulado = divide([Acumulado];[Total All Selected];0)
La representación gráfica de las medidas creadas arroja los siguientes valores:
Color dinámico
Para aplicar colores dinámicamente nos apoyaremos en una medida que devolverá como resultado un codigo de color “Hex“. Estos códigos de color podrás extraerlos de ésta página.
La medida es la siguiente:
Color Umbral =
var Umbral=max('Umbral Color'[% de Ventas])/100
var PercAc=[% Acumulado]
return
if( Umbral>PercAc; "#FF3F33"; "#43FF33")
En las dos primeras variables capturamos el valor seleccionado en el umbral (lo dividimos entre 100, pues a vamos a comparar con un %) y el % acumulado. El IF compara una con otra y devuelve el color correspondiente.
Podríamos mejorar la funcionalidad a tres colores incorporando otra variable que cogiese el mínimo del umbral y con SWITCH aplicar el color que se desee:
Color Umbral =
var UmbralMin=min('Umbral Color'[% de Ventas])/100
var UmbralMax=max('Umbral Color'[% de Ventas])/100
var PercAc=[% Acumulado]
return
SWITCH(true;
PercAc<UmbralMin; "#FF3F33";
PercAc<UmbralMax; "#43FF33";
"#FFA07A")
Esto ilustra mejor el caso del ABC:
Cómo aplicar colores dinámicos
En la tabla se hará via formato condicional sobre la medida que queramos formatear:
Especificamos que el formato lo devuelve el valor de un campo, en concreto la medida que hemos creado al efecto:
Y en el caso del gráfico en las propiedades del mismo:
Espero que te sea de utilidad.
Para descargarlo:
Pareto.pbix
Apasionado de las tecnologías de análisis de datos y Business Intelligence, y con larga experiencia en el uso de las mismas en empresas de todos los sectores, tamaños, y proyectos de diversa complejidad, en los cuales disfruto creando, planteando y desarrollando soluciones.
Tengo una curiosidad insaciable y demasiadas aficiones, entre ellas tocar el bajo con mi grupo.
Muchas gracias estimado, su paso a paso me ha servido para analizar bd en mi practica profesional. , algunos errores con las nuevas act de Pbi, en la consulta de “Umbral Color = GENERATESERIES(1;100;1)” van “,” en vez de “;”
Hola Benjamín, sí, hace algún tiempo los separadores de parámetros en las funciones de DAX para la instalación en español, eran el “;” y posteriormente cambiaron a “,”. Algunos post publicado, aunque son funcionales ya tienen algún tiempo. Gracias!
¿Como calcular el número de clientes que tienen A, que tienen B y que tienen C?
Hola Paula, disculpa la tardanza en responder. Respecto a tu pregunta, requiere una larga explicación y espero publicar una extensión del post al respecto.
Hasta entonces te sugiero que mires este post de Russo-Ferrari que plantea solución al respecto. La principal diferencia está en que aquí usan una tabla para definir dinámicamente los rangos y en el caso de mi post estos rangos se definen dinámicamente via filtros:
https://www.daxpatterns.com/dynamic-segmentation/
Un saludo
Hola Santiago buenas tardes.
mira he tratado d hacer una visualización donde me cuente la cantidad de ok y la cantidad de validos, he creado una medida
STATUS = IF([DIFERENCIA]=0,”OK”,”VALIDAR”) donde para cada persona se le asigna el valor correspondiente dependiendo del resultado, ahora quiero crear una medida donde me cuente la cantidad de ok y la cant de validos apartir de la medida creada que es STATUS. si tienees algún blog donde pueda solucionar esto te lo agradecería. Muchas gracias!!
Hola Robinson,
tal como lo planteas, creo que lo mejor sería crear una columna calculada para STATUS (es mejor hacer este paso en power query) y hacer el recuento de la misma con una medida DAX. Los ok o nok sería una dimensión o podías hacer dos medidas, una para ok y otra para nok aplicando filter. Saludos
buenos dias,
estoy intentando sacar el acumulado por cliente, pero hasta cierta fila solo lo aplica y se me repiten los numero.
me podria ayudar alguien por que me sucede.
% Importe Acumlado =
VAR Importecte = [TOTAL IMPORTE 100]
VAR ImporteGen = CALCULATE([TOTAL IMPORTE 100],ALLSELECTED(ABC_VTAS_100LBS))
return
DIVIDE(
SUMX(
FILTER(
SUMMARIZE(ALLSELECTED(ABC_VTAS_100LBS),ABC_VTAS_100LBS[Cliente],
“Importe”,[TOTAL IMPORTE 100]),
[Importe]>= Importecte),
[Importe]),
ImporteGen,0)
Hola Alvin, a falta de más detalles, puede haber varios motivos: contextos, relaciones bidireccionales…
Te sugiero que construyas las medidas por separado, como hace el post y chequear. Las medidas intermedias podrás ocultarlas para que sean visibles en el modelo, pero serán operativas a efectos del cálculo final.
Buenos días
¿se podría hacer una columna con el ranking de stock item y el % respecto al total? el objetivo es saber el 80% de ventas con qué porcentaje de stock item se realiza
Muchas gracias
Hola Rafael. Puedes crear una medida adicional que use la función RANK. Aunque no es directamente aplicable para este caso, con unas pocas modificaciones podrás adaptar la siguiente medida que pongo de ejemplo a tu caso:
Ranking =
If (HASONEVALUE(‘Art SubFamilia1′[Sub Familia 1]) && NOT ISBLANK( [Venta BI]),
RANKX ( ALL ( ‘Art SubFamilia1′[Sub Familia 1]), [Venta BI],, DESC, DENSE )
)
Esta devuelve la posición de una familia en el total de ventas. Puedes hacerlo para productos y valor de stock. Saludos
Hola Santiago,
Muchas gracias por tu propuesta, es muy útil y bien explicada. Solo un tema que podría ser útil:
¿crees que podríamos incorporar en la misma tabla el nº de productos y el acumulado del nº de productos? el objetivo sería poder tener de forma calculada que, por ejemplo, el 80% de las ventas lo generan el x% de los productos y en la gráfica poder poner % de productos vs % de ventas
Yo he intentado generar la medida % productos acum, pero no me sale bien
Muchas gracias por tu tiempo
Hola Rafael, en los comentarios de este póst creo que puedes encontrar respuesta a tu pregunta.
Gracias!
Mil y mil gracias ¡
Estuve mucho tiempo (meses) intentando hacer este pareto dinámico y no me rendía hasta que por fin encontré este Post, no es algo tan sencillo y ésta solución es perfecta.
Muchas gracias, Mónica, me alegro de que te haya resultado útil. Un cordial saludo!
Hola,
Estoy tratando de hacer una gráfica con lo que se filtra en dos slicers diferentes. La gráfica es de países y quiero que em muestre el país que se selecciona en el slicer de País 1+ lo que selecciones en el slicer País 2 para poder hacer una comparación solo entre esos dos países. Además, si se puede, calcular su diferencia y mostrarla.
Hola Sandra, se me ocurre duplicar la tabla Pais 1 (una referencia basta) y relacionarla con la tabla de hechos original. Esa segunda relacion estará inactiva pero con USERELATIONSHIP podrás manejar valores y creo que lograrías tu objetivo. En este post (Relaciones entre tablas en PowerBI y funciones para manejarlas), aunque aplicado a fechas, puedes ver el procedimiento.
Hola. La fórmula del Acumulado no me funciona. Creo que es un gran aporte, pero no me está funcionando… ¿Me podrías mandar el modelo terminado? Muchas gracias
Hola José Manuel, ya lo he solucionado y también he añadido el pbix con el ejemplo completo. Muchísimas gracias!
Hola! Como hacer un pareto con más de dos niveles?
Puedo hacer pareto a un nivel,el problema viene al desplegar hacía abajo 3 subniveles ,puedes asesorar ?
Hola José,
se podría modificar la medida y usar la función ISINSCOPE con un IF (o SWITCH) para detectar si determinada dimensión (o nivel) es visible, y en su caso hacer el cálculo que proceda para cada caso.
En nuestro ejemplo, suponiendo que agrupamos los items por color, la función “Acumulado” quedaría como sigue:
Acumulado =VAR Medida=[Total ventas]
RETURN
IF(ISINSCOPE('Dimension Stock Item'[Stock Item]);
SUMX(
FILTER (
SUMMARIZE (ALLSELECTED ('Fact Sale') ; 'Dimension Stock Item'[Stock Item] ; "VentaAcum"; [Total ventas] ) ;
[VentaAcum] >= Medida ) ;
[VentaAcum] )
;
SUMX(
FILTER (
SUMMARIZE (ALLSELECTED ('Fact Sale') ; 'Dimension Stock Item'[Color] ; "VentaAcum"; [Total ventas] ) ;
[VentaAcum] >= Medida ) ;
[VentaAcum] )
)
También habrá que representar datos en un matrix y, como podrás ver, según e nivel mostrado hace un cálculo u otro:
Al desplegar:
Espero que esto te sirva para solucionar tu problema.