Publicado el Deja un comentario

CONTAR.SI en DAX : Contar la Cantidad de Elementos que Aparecen Usando una Columna Calculada en Power Pivot

Puede que te encuentres en el siguiente desafió:

Debes contar la cantidad de veces que aparece un código en una columna. Resolver esto es sencillo en Excel, usando la función CONTAR.SI(‘columna’,’condición’)

Códigostatusconteofunction
123activo2=CONTAR.SI([Código],C4)
123apagado2=CONTAR.SI([Código],C5)
234null2=CONTAR.SI([Código],C6)
234apagado2=CONTAR.SI([Código],C7)
345null1=CONTAR.SI([Código],C8)
456activo2=CONTAR.SI([Código],C9)
456apagado2=CONTAR.SI([Código],C10)
678null1=CONTAR.SI([Código],C11)
789apagado1=CONTAR.SI([Código],C12)
890apagado1=CONTAR.SI([Código],C13)
987null1=CONTAR.SI([Código],C14)
654apagado1=CONTAR.SI([Código],C15)
Tabla ejemp

En la tabla de ejemplo, puedes verificar que la fórmula en este caso va iterando en cada celda usando como rango la columna [Código] y buscando el argumento de cada celda que se le pasa como condición.

Ten en mente lo que se expone sobre iteración, porque es el principio clave para entender la formula en DAX.

La lógica antes expuesta es la que usaremos para desarrollar la columna calcula, que igual que contar.si deberá devolver la cantidad de veces que aparece el [Código]. La medida podría escribirse de la siguiente forma.

CONTEO:=
VAR CONTEO=CALCULATE(COUNT(ejempl[Código]))
VAR COD=ejempl[Código]

RETURN 

SUMX(
	FILTER(ejempl,
			ejempl[Código]=COD),
	       CONTEO
	)
CONTAR.SI EN DAX

En las dos variables que se utilizan se integra lo que en DAX se conoce como el contexto de fila, el mismo se genera de manera automática cada vez que creamos una columna calculada.

VAR CONTEO=CALCULATE(COUNT(ejempl[Código]) : va a contar la cantidad de códigos que aparece en la columna [Código] lo cierto es que si solo usaras COUNT sin CALCULATE estarías sometido al contexto de fila automático. La manera de modificar ese contexto es usando CALCULATE.

VAR COD=ejempl[Código] : en esta variable se va a definir el contexto de fila de cada aparición de un elemento en la columna [Código]

Por último usamos un SUMX para que itere sobre el contexto modificado de las variables.

SUMX(

                FILTER(ejempl,

                                               ejempl[Código]=COD),

                CONTEO

                )

Si lees la medida desde la parte interna de la misma, notaras que FILTER devuelve una tabla cuyos valores se limitaran a cumplir la condición de: si el contexto actual es igual al contexto anterior suma las apariciones.

Algo como decir en Excel que si A2(ACTUAL)=A1(ANTERIOR) entonces suma el conteo.

El SUMX al final va iterando sobre cada tabla que va generando FILTER (también es una función de iteración) pero lo importante es que el contexto ha sido previamente modificado usando las variables.

La medida anterior se puede aplicar desde la versión 2016 de OFFICE. En caso estuvieras usando una versión anterior, usa la siguiente medida modificada.

CONTEOV2:=CALCULATE(
	COUNT(ejempl[Código]),
		FILTER(ejempl, 
			ejempl[Código]=EARLIER(ejempl[Código])
		)	
CONTAR.SI en DAX versiones de office anterior a 2016

Si te gustó este post, recuerda que puedes suscribirte de manera gratuita y recibirás información valiosa para tu desempeño profesional.

La bondad es la única inversión que nunca falla.

H.D. THOREAU
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