Stock, conjuntos de productos y M2M. Problemas de diseño y DAX

[zztxcxc]

¡Hola!

El problema que estoy tratando de resolver es el modelado adecuado para calcular el stock actual en conjuntos de productos y es un poco difícil. (También tenemos el modelo de ventas, pero es mucho más sencillo calcular las ventas en los conjuntos de productos allí).

En nuestro modelo de inventario tenemos stocks calculados utilizando varias tablas de hechos (inventario, tránsito, reserva). Son bastante similares en términos de estructura, pero para obtener stock disponible tenemos que calcular una medida, que combina los datos de todos ellos (por ejemplo, stock - inventario - tránsito - reserva). Todas las tablas son instantáneas diarias de mercancías.

Ahora las empresas no sólo quieren ver stock/tránsito/reservas a reducir, sino también ser capaces de recalcularlos en conjuntos de productos dependiendo de los filtros actuales.

Un conjunto de productos es cuando se combinan varios productos en un solo paquete y se vende como una sola unidad. Así que el departamento de suministros quiere saber cuántos conjuntos pueden obtener del almacén. Las existencias se calculan de manera que podemos distinguir los productos con la misma clave de diferentes conjuntos (es decir, hay un producto A y puede estar en diferentes conjuntos de productos: establecer "Tipo 1" y establecer "Tipo 2". Cuando se calculan las existencias, se sabe que las unidades X se podrían combinar en el conjunto"Tipo 1" y las unidades Y podrían combinarse en el conjunto"Tipo 2",por lo que X+Y será total stock abailable).

Antes de pasar al modelo de inventario, permítanme explicar cómo calculo los conjuntos de productos vendidos en el modelo de ventas: funciona bastante simple - ya que no se puede vender una parte de un conjunto, tenemos el modelo creado de la siguiente manera:

La tabla Product Set Bridge es una combinación de Producto, Conjunto y Units_Qty_Per_Set. Por ejemplo:

Set_Key	Product_Key	Qty_Per_Set
Set A	11111	1
Set A	22222	3
Set A	3333	1

Como se puede ver en la tabla, el conjunto A contiene 5 productos. En ventas conocemos Product_Key, Set_Key y QTY vendidos, por lo que podemos calcular el número de conjuntos de productos vendidos.

La medida se calcula de esta manera:

Sales in Product sets:= 
   SUMX (
            ADDCOLUMNS (
                VALUES ( 'Product Set'[Set_Key] );
                "Numer of sets";
                    CALCULATE (
                        MINX (
                            ADDCOLUMNS (
                                SUMMARIZE (
                                    'Sales';
                                    'Product Set'[Product_Key];
                                    'Product Set'[Qty_Per_Set]
                                );
                                "Min_Sets"; DIVIDE ( [Sales Quantity] ; [Qty_Per_Set] )
                            );
                            [Min_Sets]
                        )
                    )
            );
            [Numer of sets]
        )

(para protegernos calculamos la cantidad mínima dentro de cada conjunto y ellos resumen los valores mínimos para cada conjunto por si acaso)

Sin embargo, esta fórmula en el modelo de inventario no funciona y es por eso que:

1) Usted puede tener la cantidad de stock que no se puede combinar en un número entero de conjuntos (es decir, podemos 3 unidades de cada producto del conjunto A de la tabla de puentes arriba disponible, lo que significa que podemos ge un conjunto de productos completo y 2 unidades de cada producto 11111 y el producto 22222 a la izquierda sin ser combinados en cualquier conjunto.

2) Debemos calcular la cantidad de conjuntos dependiendo de los filtros actuales. Digamos que el producto 11111 y 22222 se almacenan en Canadá y el producto 3333 se almacena en Italia. Si un usuario filtra por país, que debería ver 0/blank(), porque no se puede obtener un conjunto completo dentro del filtro actual contex

3) Esto es más coplex: cuando los usuarios dividen la medida por producto, el todavía debe ver el número de conjuntos, que podemos obtener teniendo en cuenta todos los productos del conjunto actual Y el contexto de filtro actual. Echemos un vistazo a cómo debe ser:

Tenemos acciones:

Producto 11111 - 3

Producto 22222 - 3

Producto 3333 - 3

Si dividimos [Stock In Sets] medida por sets y productos, deberíamos ver los resultados:

Establecer	Producto	Stock_In_Sets
Set A	11111	1
	22222	1
	333	1

Por lo tanto, como se puede ver, incluso dentro de un solo producto debe tener en cuenta el resto de productos de un conjunto.

Así que mi primer intento fue diseñarlo de la misma manera que está en el modelo de ventas (esto será sólo un ejemplo de lo que hice para fines de prueba):

Después de un tiempo se me ocurrió una medida, que es bastante compleja, pero resuelve el problema

Stock in Sets:=
CALCULATE(
	VAR SetsFilteredByProduct =
	SUMMARIZE(
		FILTER(
			'Bridge_Product_Set_Product';
			'Bridge_Product_Set_Product'[Product_Key] IN Values(Dim_Product[Product_Key])
		)
		;'Bridge_Product_Set_Product'[Product_Set_Key]
		
	)
	VAR SetsWithAllProducts=
		SUMMARIZE(
			FILTER(
				all('Bridge_Product_Set_Product')
				;'Bridge_Product_Set_Product'[Product_Set_Key] in SetsFilteredByProduct 
			
			);
			'Bridge_Product_Set_Product'[Product_Key]
		)
	VAR Sets =
	CALCULATETABLE(
		 ADDCOLUMNS (
			SUMMARIZE (
			    'Bridge_Product_Set_Product';
		    	'Bridge_Product_Set_Product'[Product_Set_Key];
		        'Bridge_Product_Set_Product'[Product_Key];
		        'Bridge_Product_Set_Product'[Product_Quantity_Per_Set]
		        );
		    "Sets_Qty";
		        QUOTIENT (
		            [Stock];
		            'Bridge_Product_Set_Product'[Product_Quantity_Per_Set]
		        )
		)
		;FILTER(
			ALL('Bridge_Product_Set_Product');
			'Bridge_Product_Set_Product'[Product_Set_Key] IN SetsFilteredByProduct	
		)
		;FILTER
		(
			ALL('Dim_Product')
			;'Dim_Product'[Product_key] IN SetsWithAllProducts
		)
	)
	VAR MinBySet = 
	  GROUPBY (
	            Sets;
	            'Bridge_Product_Set_Product'[Product_Set_Key];
	            "Min Sets"; MINX ( CURRENTGROUP (); IF(ISBLANK([Sets_Qty]);0; [Sets_Qty]) )
	        )
	VAR TotalSets = 
	SUMX (
		MinBySet;
		[Min Sets]
	)
	VAR Result = IF(TotalSets = 0; BLANK(); TotalSets)
	return
		Result
)

Sin embargo, hay un caso, cuando esta medida no funciona (otro conjunto de ejemplos):

La pregunta es: ¿debo reescribir de alguna manera la medida para que funcione (no hay idea de cómo) o cambiar el diseño a algo como esto (poner la tabla de puente entre hechos y dim.product):

Pero esto nos lleva a otro conjunto de problemas:

1) Nuestro Dim.Product es SCD Tipo 2, así que tendremos que generar todas las combinaciones de versión del producto dentro de un conjunto

2) Hay productos que no están en ningún conjunto, por lo que tendremos que emular esto.

Me parece que es un problema M2M, pero todavía no puedo hacer frente a él

[CheenuSing]

Hola @zztxcxc ,

¿Es posible cargar el archivo pbix con datos de muestra y salida esperada a One Drive o Google Drive y compartir el enlace aquí.

Salud

CheenuSing