Muéstreme todas las facturas que contienen este producto

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: Avanzado

Estaba respondiendo una pregunta interesante en el foro la semana pasada y, como suele ser el caso, pensé que sería un artículo interesante para compartir con todos ustedes aquí. He modificado un poco el escenario para este artículo y he desarrollado algunas medidas de prueba para ayudar a explicar algunos conceptos.

Definición del problema

Aquí está el escenario. El usuario quiere seleccionar un producto de una lista. Cuando se selecciona ese producto, quieren ver una lista de todas las facturas que contienen ese producto Y VER TAMBIÉN TODOS LOS DEMÁS PRODUCTOS en esas mismas facturas. La razón por la que este es un problema tan interesante es que normalmente cuando selecciona productos de una lista en Power Pivot, los productos seleccionados filtran el modelo de datos, por lo tanto, solo puede ver las ventas de esos productos seleccionados.

Data de muestra

He producido el siguiente modelo de datos en Power Pivot para Excel. Esta solución también puede funcionar en Power BI, por supuesto. Puede ver que la tabla de productos está unida a la tabla de facturas a través del ID de producto (1 a continuación) y puede ver los datos de muestra en las 2 tablas de la derecha (2 a continuación).

image_thumb-30-6526013

Por qué esto es complicado

Como puede ver en la imagen a continuación, cuando se agrega una cortadora a la tabla de productos y luego selecciona un artículo en la cortadora, la tabla de detalles de la factura SÓLO muestra ese único producto seleccionado. Pero el requisito es ver TODOS los artículos en TODAS las facturas que contienen ese producto seleccionado.

video_2018-04-25_074324_thumb-7750078

Es de esperar que, a partir de la imagen anterior, quede claro que el filtrado natural de Power Pivot está trabajando en contra de producir la solución requerida, por lo que debe haber un enfoque diferente.

Mi enfoque para una solución de trabajo

Lo más importante que debe comprender acerca de Power Pivot es cómo funciona el filtrado. Como acabo de describir anteriormente, el comportamiento de filtrado natural es que cuando se coloca un filtro en una tabla de búsqueda (Productos en este caso), ese filtro se propaga a la tabla de datos a través de la relación. El truco para resolver este problema, por lo tanto, implica los siguientes 4 pasos.

  1. Detenga la cortadora que filtra el modelo de datos
  2. «Detectar» lo que se ha seleccionado en la segmentación
  3. Cree una tabla virtual de números de factura que contenga los productos seleccionados del paso 2
  4. Utilice esta tabla virtual de números de factura como filtro para pasar a la medida

Permítanme recorrer cada uno de estos 4 pasos para crear una solución funcional.

1. Detenga el Slicer que filtra el modelo de datos

La solución que se me ocurrió aquí fue cargar una segunda copia de la tabla de productos y cargar esto para el modelo de datos.

image_thumb-31-4640326

Puede ver en la imagen de arriba que la tabla ProductsDisconnected no tiene relación con la tabla Sales. Esto es perfectamente válido en Power Pivot: las tablas no tienen que estar conectadas, de ahí el nombre de «mesa desconectada».

Mi nuevo modelo de datos extendidos se muestra a continuación. La lista de productos en la segmentación (1 a continuación) proviene de la tabla desconectada, por lo tanto, esta segmentación no tiene ningún efecto de filtrado en el informe (tabla dinámica). La lista de productos en la tabla dinámica proviene de la tabla Productos (2 a continuación).

image_thumb-32-1355514

Nota: Si está usando Power BI, puede cambiar la forma en que una segmentación interactúa con un objeto visual (desactivar el filtrado cruzado). Esto no es posible en Excel, por lo tanto, he utilizado el enfoque descrito anteriormente que funciona en ambas herramientas.

super-charge-power-bi-ad_1-1024x128-6595595

2. Detectar productos seleccionados en Slicer

Cuando el usuario hace clic en la cortadora, no sucede nada (por supuesto) porque la cortadora no está conectada al resto del modelo de datos. Sin embargo, es posible “recolectar” las selecciones de la cortadora usando la función VALORES. VALUES devuelve una tabla virtual que contiene una lista única de todos los elementos en el contexto de filtro actual (los elementos seleccionados en la segmentación desconectada en este caso). Uno de los desafíos al usar las funciones de tabla en Power Pivot es que no puede «ver» la tabla, por lo que puede ser difícil entender lo que está sucediendo. Tome la fórmula DAX válida a continuación.

= VALORES (Productos desconectados[Descr])

Si trato de escribir esta fórmula válida como una medida y la agrego a la tabla dinámica, arrojará un error. No puede agregar una «tabla» a un objeto visual (por ejemplo, una tabla dinámica) porque no puede colocar una tabla en una sola celda en un objeto visual. Una celda espera un valor escalar, no una tabla (que es un objeto bidimensional). Lo que me gusta hacer (para ayudar a visualizar lo que está sucediendo) es escribir medidas de prueba para poder «ver» lo que está sucediendo. Todo lo que tengo que hacer es convertir la tabla devuelta por VALUES en un valor escalar. Aquí está mi medida de prueba.

Selected Products = 
      CONCATENATEX(
           VALUES(ProductsDisconnected[Descr]), 
           ProductsDisconnected[Descr], 
           ", "
      )

Como puede ver a continuación, la medida “Productos seleccionados” ahora muestra todos los valores seleccionados en la cortadora cortesía de la función de prueba CONCATENATEX – confirmación de que la función VALORES efectivamente devuelve una tabla de los artículos seleccionados.

selected-products_thumb-3180723

3. Cree una tabla de números de facturas

Ahora que he cosechado con éxito los productos que selecciona el usuario mediante VALORES, es cuestión de creando una tabla virtual de los números de factura que contienen esos productos. Estoy seguro de que hay muchas formas de hacer esto, pero la solución que se me ocurrió es la siguiente. Tenga en cuenta que este código no funcionará como está: este código produce una tabla y no se puede agregar una tabla a una tabla dinámica (como se mencionó anteriormente).

= CALCULATETABLE(
      VALUES(Sales[Inv #]),
      INTERSECT(
         ALL(Products[Descr]), 
         VALUES(ProductsDisconnected[Descr])
      )
  )

Puede ver en la línea 4 anterior que estoy usando la función INTERSECT. Estoy pasando la lista de elementos seleccionados de la cortadora a los Productos[Descr] columna de la tabla Productos. La tabla Productos está conectada a la tabla Ventas. Entonces, después de que este filtro se pasa a la tabla Productos, la función CALCULATETABLE (línea 1) propaga este filtro a la tabla Ventas, y luego VALORES (línea 3 arriba) extrae una lista de los números de Factura que contienen estos productos seleccionados. Es importante destacar que esta nueva tabla virtual creada por CALCULATETABLE y VALUES (línea 3) conserva el linaje del modelo de datos. Lo que esto significa es que la nueva mesa virtual tiene una relación virtual de uno a muchos entre la mesa virtual y la mesa de donde nació (Ventas en este caso). Si esta nueva tabla virtual creada por CALCULATETABLE se usa luego como una función de filtro dentro de un CALCULATE, tendrá el resultado de filtrar la tabla de Ventas para los productos en la lista (en la tabla virtual).

4. Utilice la tabla de números de facturas como filtro

El último paso es utilizar esta tabla virtual anterior (la lista de números de factura) como filtro para devolver las ventas de cada artículo. Aquí está mi fórmula final.

= CALCULATE(
      [Total Qty],
      CALCULATETABLE(
          VALUES(Sales[Inv #]),
          INTERSECT(
             ALL(Products[Descr]), 
             VALUES(ProductsDisconnected[Descr])
          )
      )
  )

Esta vez estoy usando la tabla creada por CALCULATETABLE (líneas 4 – 7) como filtro para un CALCULATE. Una vez aplicada la tabla virtual, CALCULATE extrae la cantidad total de ventas de todos los artículos de las facturas que contienen el producto seleccionado.
final-slicer_thumb-8301574

Puede descargar mi workbook de muestra aquí si desea ver más de cerca => Libro de trabajo de muestra

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.