Publicado el Deja un comentario

Análisis ABC Pareto en DAX y SQL Server

El análisis ABC se deriva del principio de Pareto del 80/20 la teoría dice que el 20% de tu esfuerzo generan el 80% de los resultados. Según este principio el 80% de tus ganancias puede estar concentrado tan solo en el 20% de tus productos (aplica en inventarios, ventas, costo, entre otros).

En este post veremos cómo realizar el análisis ABC  usando DAX  y SQL Server, no se trata de comparar que es lo más óptimo o deducir que es mejor. Como debes saber todo depende del contexto en que realices el análisis; las herramientas que tengas disponibles y tu experticia al momento de encontrar soluciones.

Los datos que estamos usando provienen de la BDD de pruebas AdventureWorksDW2017. Mira el post sobre bases de datos para SQL Server, MySql, con las que puedes entrenar en el Post:

ABC en SQL SERVER:

En la primera versión de esta consulta usaré un WITH Comon Table Expresion (CTE), para generar una tabla temporal donde se agrupa la cantidad de venta por modelo de producto. La tabla de nombra como VentaProductos  

USE AdventureWorksDW2017
GO

WITH ProductSales AS
(
--Cantidad de ventas por modelo

SELECT
pto.[ModelName],
SUM(va.[SalesAmount]) Ventas
FROM [dbo].[FactInternetSales] va
INNER JOIN [dbo].[dimProduct] pto
ON va.[ProductKey] = pto.[ProductKey]
GROUP BY
pto.[ModelName]
)
SELECT
ps.[ModelName],
ps.[Ventas],
SUM(ps.[Ventas]) OVER (ORDER BY ps.[Ventas] DESC) AS VentaAcumulada,
--En una tabla ordenada DESC la suma del acumulado
SUM(ps.[Ventas]) OVER () AS TotalVentas,
--el valor total de venta para el que se divide el acumulado
SUM(ps.[Ventas]) OVER (ORDER BY ps.[Ventas] DESC) / SUM(ps.[Ventas]) OVER () AS PorcetajeAcumulado,
--el valor porcentual de ventas acumulada para total ventas
CASE
WHEN SUM(ps.[Ventas]) OVER (ORDER BY ps.[Ventas] DESC) / SUM(ps.[Ventas]) OVER () <= 0.7
THEN 'A'
WHEN SUM(ps.[Ventas]) OVER (ORDER BY ps.[Ventas] DESC) / SUM(ps.[Ventas]) OVER () <= 0.9
THEN 'B'
ELSE 'C'
-- la condicion para clasificar A, el 20% B y el restante se clasifica como C
END AS Clasif
FROM ProductSales ps
GROUP BY
ps.[ModelName],
ps.[Ventas];

--Los modelos clasificados como A, son los que mayor ganancias generan. 

ABC con DAX en Power Pivot

Para realizar el mismo ejercicio usando Power Pivot, se va requerir usar columnas calculadas, esto va ser posible gracias al contexto de fila que se puede aplicar en una tabla con CALCULATE. De la misma forma se va utilizar la BDD AdventureWorksDW2017 y trae al modelo de datos la tabla Ventas, y Productos, el modelo se ve de la siguiente manera

Se debe  crear las siguientes columnas calculadas. SumaCantidadVenta, VentaModelo, VentaAcumModel, %deTotal y ClasifABC, todo esto lo realizamos en la tabla de DimProducts.

SumaCantidadVenta:=
SUMX(
	RELATEDTABLE(FactInternetSales),
		FactInternetSales[SalesAmount])
VentaModelo:=
SUMX(
	FILTER(DimProduct,DimProduct[ModelName]=
			EARLIER(DimProduct[ModelName])),
				DimProduct[SumaCantidadVenta]
		)
VentaAcumModel:=
      SUMX(
	FILTER(DimProduct,
		DimProduct[VentaModelo]>=
			EARLIER(DimProduct[VentaModelo])
			),
		         DimProduct[SumaCantidadVenta]
	)
%deTotal:=
     DIVIDE(
	[VentaAcumModel],
			SUM(DimProduct[SumaCantidadVenta]
			))
ClasifABC:=
      IF([%deTotal]<=0.7,"A",
	   IF([%deTotal]<=0.9, "B",
			"C")
                    )

Luego de generar las columnas calculadas la tabla DimProducts se vería así:

Haciendo una comparación entre la tabla que resulta de la consulta SQL Server y generando una tabla dinámica de las medidas en DAX el resultado es el mismo.

Conclusión

En ambos casos llegaremos al mismo resultado, es un ejercicio muy interesante de realizar si estás iniciando con SQL o DAX. Como te decía en el inicio de este post no pretendo decirte qué o cuál es mejor. Depende del contexto de tus datos, y de las tecnologías que tengas al alcance. En lo personal y como usuario más habitual de Excel prefiero DAX. Pero si te enfrentas a un contexto de de datos masivos, puedes generar la consulta de SQL e importarla a Power Pivot o Power Query directamente.

Si este artículo te gusto, te invito a que te suscribas al portal y recibirás información interesante sobre técnicas de análisis de datos.

Las matemáticas no mienten, lo que hay son muchos matemáticos mentirosos.

H.D. thoreau

Publicado el Deja un comentario

Buenas Prácticas en Informes con Excel: Gráficas y Tablas Dinámicas

Los informes que se presentan en Excel deben ser cuidadosamente pensados, para que cumplan al menos el 50% de la expectativa de los usuarios que los van a revisar. Es común que como analistas cometamos ciertos errores en la presentación, ya sea por inexperiencia o porque nuestra formación en Excel solo haya sido superficial. En todo caso no es excusa para que no trates de mejorar constantemente tus habilidades. El mandamiento de la era digital es que debes estar dispuesto al cambio y aceptar nuevas realidades a cada momento.

Para que tengas un mejor enfoque, en este post hablaremos de las prácticas que harán que tus gráficos y tablas dinámicas se complementen y sean visualmente más atractivas.

A lo largo de tu vida laboral te encontraras con una variedad de desafíos que te ayudaran a afinar tus habilidades, te planteo algunos de esos consejos que me han ayudado a mejorar:

Cuánta información mostrar      

Mal

No lo muestres todo en una tabla ni tampoco en una sola gráfica: es muy fastidioso ver informes excesivamente cargados de datos, con gráficos amontonados y tratando de mostrar demasiadas categorías de una sola vez.

En el siguiente ejemplo de gráfico de la parte superior. Datos redundantes, gráfico saturado y confuso, el usuario tardara al menos dos o tres explicaciones en entender cuál fue tu objetivo con ese reporte.

Bien

Resume los datos para el gráfico: si vas a mostrar datos históricos usa un gráfico de barras para mostrar los años, y una tabla con valores acumulados para mostrar los colores. Mira la diferencia en el gráfico y tabla de la parte inferior. Si lo necesitas no dudes usar una segmentación para que apoye el argumento de tus análisis.

BDD entrenamiento Contoso

Formato estándar vs Personalizado

Mal

No exageres con los colores, ni uses texturas en gráficos. Sé que muchos no tenemos la habilidad de combinar colores (es toda una ciencia créeme) y esta limitante puede jugarnos malas pasadas en ocasiones. En el siguiente gráfico de pastel, alguien estuvo inspirado.

Bien

Ten en cuenta que los colores estándar que ofrecen los estilos en ocasiones no reflejan los colores corporativos, y tus informes serán más atractivos si en lo posible muestras que te sientes orgulloso de tu trabajo, usando los colores de tu organización. Nada más no exageres. Mira el ejemplo de la parte inferior de la imagen

Tabla dinámica y referencias a celda juntas

Mal

Mostrar un informe que combina mitad tabla dinámica y mitad referencias de celda, es una pésima idea, te lo ruego no lo hagas. En primer lugar demuestra tu falta de conocimiento en herramientas de Excel, y por otra parte da a entender que fue un trabajo apresurado y sin suficiente dedicación. Si parece una buena idea al principio, pero se puede tornar en un problema gordo al momento de evaluar los datos.

Bien

Si tus datos conectan varias tablas, demuestra tu profesionalismo creando un modelo de datos con la ayuda de Power Pivot, esta herramienta viene integrada en Excel desde la versión 2013, mostrar un solo formato en tu reporte, que sea dinámico y comprensible es lo que debes buscar.

También Considera:

  1. Realizar un listado de preguntas y respuestas que tu informe ayudara  analizar, pro ejemplo: Le valor de ventas más importante se concentra en ‘país, ciudad’; el cliente que más compras ha realizado es ‘cliente1’; los productos de color ‘color1’ son los preferidos en ‘ciudad’; etc..
  2. Si el reporte es para gerencia, recuerda quitar los decimales de la tabla dinámica, cuando proyectas las tablas las cifras de miles y decimales suelen resultar un poco confusas.
  3. Recuerda usar colores corporativos, pero mientras desarrollas tu informe ten en mente como se vera proyectado en una pantalla, los colores fuertes contrastados con colores blancos de fuente suelen ser muy favorables, no uses amarillos ni tonos débiles de color de fondo de tablas.
  4. Recuerda validar, validar, y nuevamente validar tus datos, recuerda que los resúmenes deberían estar conciliados con los valores a nivel de SKU, para que no existan diferencias en la información. Te han confiado un reporte, nadie lo revisará previamente, nadie te dirá que existe o no un error antes de reunión de revisión (seamos sinceros ya no estás en la universidad), es mejor tardar un poco más en el análisis y conciliación antes que descubrir un error en plena revisión con muchas más personas.

Si te gusto este post, puedes suscribirte a nuestro portal y recibirás de primera mano información actualizada de tecnología y análisis de datos.

Eres más consciente que antes de lo que es importante y lo que es trivial. ¡Vale la pena esperar al futuro! – H.D. Thoreau