SUMX o CROSSJOIN anidados cuádruples

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

Contenidos

Nivel: Intermedio

Tuve un problema interesante con un cliente la semana pasada en el que construí un modelo de datos para calcular los reembolsos pagaderos a los clientes en función de su volumen de ventas. Hubo algunos desafíos que voy a cubrir a continuación, que incluyen:

  1. Es necesario que exista una forma sencilla de capturar los datos de los reembolsos, pero luego debe transformarse para que pueda usarse en el modelo.
  2. Hay un problema SUMX anidado: anidamiento cuádruple en este caso. Resuelvo esto con un CROSSJOIN (y luego SUMMARIZE) que explicaré a continuación.
  3. También hay un problema IF (HASONEVALUE ()) anidado cuádruple que resuelvo con una fórmula innovadora que también cubriré a continuación.

El escenario empresarial

El escenario es un negocio mayorista que vende productos a los clientes. Dependiendo del contrato con el cliente, se puede pagar un reembolso en partes de la venta. El objetivo es crear una herramienta que calcular correctamente el reembolso pagadero en todos los niveles de agregación en una tabla dinámica.

El modelo de datos

Para demostrar el problema, he construido un modelo simplificado que ilustra el problema y las soluciones. Eso Vale la pena señalar que se trata de un modelo simplificado; en realidad, la situación era más compleja en varios niveles. No he incluido esas complejidades en esta publicación, ya que hacerlo elevaría la dificultad a «avanzado» y perdería la oportunidad de aprendizaje «intermedio» de DAX. He subido mi libro de demostración aquí si quieres echarle un vistazo.

Los 4 niveles de granularidad

La complejidad de este problema proviene de los 4 niveles de granularidad (había más niveles en el problema real, pero estos 4 demuestran bien el problema). La siguiente tabla ilustra esta granularidad. El requisito es pagar a cada cliente un% de descuento sobre el volumen de ventas. El reembolso al cliente varía según la división, el proveedor y la categoría de producto.

image_thumb-6-7845089

La tabla anterior muestra una forma fácil de usar para capturar los datos de reembolso del usuario. La tabla tiene categorías como columnas: excelente para la entrada de datos, pero no tan bueno para Power Pivot. Luego utilicé Power Query para desvincular estos datos por lo que se ve así.

image_thumb-7-7056757

La tabla anterior es mucho mejor para Power Pivot: una sola columna para cada una de las 4 dimensiones (División, Cliente, Proveedor, Categoría) y una sola columna que tiene la tasa de reembolso.

La mesa de ventas

La tabla de ventas fue bastante sencilla. Construí una tabla que está en la misma estructura que la tabla de reembolsos final anterior desde el almacén de datos de ventas como se muestra a continuación.

image_thumb-8-9068614

Usé las técnicas de SQL para usuarios de Excel I cubierto en esta publicación de blog para dar forma a la mesa en carga, ya que el almacén de datos no tenía estos datos de nivel de resumen almacenados de forma nativa.

Las tablas de búsqueda

Luego necesité 4 tablas de búsqueda para poder unir ambas tablas de datos y hacer que funcionen juntas. Usé la técnica cubierta en esta publicación de blog aquí para unir las 2 tablas de datos a las tablas de búsqueda y terminé con esto.

image_thumb-9-5029092

Tenga en cuenta que este es un modelo de datos simplificado para demostrar algunos conceptos. Si el ejemplo de la vida real fuera el mismo, sería posible (incluso fácil) combinar las 2 tablas de datos en una sola tabla en Power Query y simplificar el problema. Pero eso perdería la oportunidad de aprender algunos DAX más complejos, y las otras complejidades del escenario real impiden que esta sea una opción viable de todos modos.

La medida de las ventas es fácil

El cálculo del valor de las ventas totales es sencillo.

Total Sales = SUM(Sales[Value])

Además, esta medida es totalmente aditiva. Por lo tanto, no importa el nivel de granularidad que tenga en la tabla dinámica, los totales siempre se desplazarán correctamente hacia arriba o hacia abajo para reflejar los totales correctos en todos los niveles como se puede ver en la tabla dinámica a continuación.

image_thumb-10-7409625

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

La medida de recolección de reembolsos

La tabla de descuentos es más compleja. Lo primero que debe hacer es «recolectar» la tasa de reembolso correcta de la tabla de reembolsos para que pueda multiplicarse por las ventas en la tabla anterior. La medida bruta parece bastante básica en primera instancia.

Rebate Rate = SUM(Rebates[Rate])

Esta fórmula utiliza la función SUM como un truco para «recolectar» el valor de la tabla. (dado que no puede usar una columna desnuda dentro de una medida como esta). Es posible usar cualquier medida de agregación (podría haber usado MIN, MAX, AVERAGE o incluso VALUES) para hacer esto. Seleccioné SUM en este caso para ilustrar fácilmente el problema de agregación; si hubiera usado uno de los otros, el problema sería mucho más difícil de detectar.

A diferencia de la tabla de ventas anterior (que es completamente aditiva), estas tasas de reembolso no son aditivas en absoluto. Solo tienen sentido en el nivel de granularidad de División, Categoría, Proveedor y Cliente. – no hay forma de agregarlos correctamente en este momento (podríamos hacer un promedio ponderado más adelante, pero eso está fuera de alcance).

Por lo tanto, puede ver en la tabla dinámica a continuación que las filas y columnas del total general están sumando incorrectamente estas tasas.

image_thumb-11-6984920

Cuádruple IF (HASONEVALUE ())

Como puede ver en la tabla dinámica anterior, los subtotales y totales generales agregan incorrectamente las tasas de reembolso y proporcionan valores incorrectos en los niveles agregados. Quería obligar a la tabla dinámica a devolver solo un valor con la granularidad correcta (y devolver espacios en blanco de lo contrario para evitar confusiones). La forma en que normalmente hago esto es usar un patrón IF (HASONEVALUE ()). Este patrón funciona bien en una sola dimensión, pero en este caso necesitaría anidar 4 de estos patrones entre sí para evitar correctamente que las agregaciones se produzcan en todos los casos. La fórmula se vería así.

= IF(
    HASONEVALUE(Division[Divison]),
    IF(
        HASONEVALUE(Suppliers[Supplier]),
        IF(
            HASONEVALUE(Customers[Customer]),
            IF(HASONEVALUE(Category[Category]), SUM(Rebates[Rate]))
        )
    )
)

Mientras pensaba en el problema de lo que realmente estaba tratando de hacer, se me ocurrió una solución innovadora que era menos compleja de entender. Me di cuenta de que la tasa de reembolso solo tenía sentido si solo había un valor para todas las tablas de dimensiones. Así que simplemente multipliqué el número de filas visibles en el contexto del filtro para cada tabla entre sí. Si la respuesta es 1, entonces la tasa de reembolso es válida. Entonces, la nueva fórmula que creé fue esta.

= IF(
   COUNTROWS(Division) * COUNTROWS(Suppliers) 
       * COUNTROWS(Customers) 
       * COUNTROWS(Category) = 1,
   SUM(Rebates[Rate])
)

editar: finales del 16 de marzo. He estado pensando, supongo que podría hacer algo como esto: no lo he probado, ¡pero al formateador DAX parece gustarle!

= IF(
    HASONEVALUE(Division[Divison]) &&
    HASONEVALUE(Suppliers[Supplier]) &&
    HASONEVALUE(Customers[Customer]) && HASONEVALUE(Category[Category]), SUM(Rebates[Rate])
)

Luego, la tabla dinámica se actualiza y ya no devuelve valores incorrectos en los niveles agregados, como se muestra a continuación. Esto facilita ver la tasa de reembolso en cualquier tabla dinámica y no muestra valores de agregación incorrectos.

image_thumb-12-6347699

Hora de multiplicar las medidas

El siguiente paso es multiplicar las Ventas Totales por la Tasa de Reembolso para calcular el monto a pagar. Escribí una medida simple de la siguiente manera y luego obtuve la tabla dinámica a continuación (amarilla).

Total Rebate Payable Wrong = [Total Sales] * [Rebate Rate]

Tenga en cuenta que no hay subtotales ni totales generales, no es lo que quiero. El problema es que el filtrado que apliqué en el [Rebate Rate] La medida anterior está filtrando los totales agregados. Este filtrado es bueno para evitar que la tabla dinámica muestre agregaciones incorrectas de la tasa en la tabla verde anterior, pero está causando un problema aquí.

image_thumb-13-5023445

Entonces, el siguiente paso que hice fue modificar la medida de Reembolso total a pagar de la siguiente manera (eliminando el filtro de agregación y simplemente usando la medida sin procesar original).

Total Rebate Payable Wrong = [Total Sales] * SUM(Rebates[Rate])

Ahora para el problema y el propósito de esta publicación

Esta nueva medida ahora está creando subtotales y totales generales como se muestra a continuación. Pero el problema es que estos totales no son correctos. Tome la columna de datos resaltada que se muestra a continuación. Si suma los reembolsos para el Cliente A, Proveedor 2 = $ 34,73 y Cliente B, Proveedor 2 = $ 21,98, obtendrá $ 56,71. Pero el total de la columna dice $ 112.13

image_thumb-14-7958359

El valor en el total general de esta columna es en realidad (Cliente A, Reembolso del Proveedor 2 + Cliente B, Reembolso del Proveedor 2 3,3%) * (Ventas combinadas para ambos clientes $ 3,398) = $ 112.13. Esta es simplemente la forma en que funcionan Power Pivot y Pivot Tables, así que no luche contra eso, pero aprenda a trabajar con él. Tan pronto como elimine uno de los filtros de la tabla dinámica (como sucede automáticamente con los subtotales y los totales generales) entonces todo se derrumba. Este es ahora el problema clásico que normalmente se resuelve con SUMX o aplicando filtros en TODAS LAS DIMENSIONES en la tabla dinámica.

La solución SUMX cuádruple anidada

SUMX simplificado

SUMX resuelve estos problemas iterando sobre todos los valores posibles en una tabla (o columna convertida en una tabla con VALORES) y hace el cálculo en cada nivel de granularidad. Por ejemplo, si tomamos un ejemplo simplificado en el que solo hay «clientes» (es decir, no hay proveedores, categorías, divisiones), la siguiente fórmula calcularía correctamente el reembolso a nivel granular, así como los totales.

Simple SUMX Solution =
   SUMX(Customers, [Total Sales] * CALCULATE(SUM(Rebates[Rebate])))

La fórmula anterior itera sobre la tabla Clientes. El [Total Sales] la medida tiene un CALCULATE implícito envuelto alrededor de él, y he incluido un CALCULAR alrededor de los Descuentos[Rate] porción de la fórmula. Estos 2 CÁLCULOS fuerzan la transición de contexto desde el contexto de fila (creado por SUMX) en la tabla de clientes en un contexto de filtro que filtra tanto la tabla Ventas como la tabla Reembolsos. El resultado neto es una fórmula que calcula la respuesta correcta a nivel de cliente independientemente del nivel de granularidad en la tabla dinámica (para este ejemplo simplificado de todos modos).

SUMX cuádruple

Pero, por supuesto, este ejemplo simplificado solo resuelve el problema de granularidad en una de las dimensiones, y tengo 4 en este ejemplo. Entonces, una forma de resolver el problema es anidar 4 fórmulas SUMX juntas de la siguiente manera:

Total Rebate Payable SUMX =
    SUMX(Customers,
         SUMX(Category,
              SUMX(Suppliers,
                   SUMX(Division, [Total Sales] * CALCULATE(SUM(Rebates[Rate])))
              )
          )
    )

Esta fórmula resuelve los problemas y da las respuestas correctas, como se puede ver en la siguiente tabla dinámica. Me gusta pensar en SUMX como una «simulación» del comportamiento de filtrado de una tabla dinámica en la memoria para que siempre calcule el resultado correcto.

image_thumb-15-5470187

Pero hay muchos problemas con esta fórmula, uno de los cuales es que es muy ineficiente. Esto no es un problema en los modelos de datos pequeños, pero es un problema cuando se obtienen más datos. Además, la fórmula es fea.

Ingrese CROSSJOIN

Ahora es el momento de mostrarte la fórmula alternativa CROSSJOIN (que también funciona). Hay una fórmula aún mejor al final de la publicación.

Total Rebate Payable =
    SUMX(
          CROSSJOIN(Customers, Category, Suppliers, Division),
          [Total Sales] * CALCULATE(SUM(Rebates[Rate]))
   )

Esta fórmula también usa SUMX, pero esta vez solo hay una función SUMX. SUMX iterará sobre una tabla temporal creada por CROSSJOIN. La tabla temporal contiene una lista de todas las combinaciones posibles de las 4 tablas, exactamente lo que queremos hacer para obtener el nivel más bajo de granularidad. La forma más fácil de mostrar esto es iniciar DAX Studio y construir la tabla. Vea los resultados a continuación.

image_thumb-16-1064572

Esta tabla tiene 60 filas en total, todas las combinaciones posibles. Cuando se usa la tabla CROSSJOIN dentro de la fórmula SUMX, sucede algo muy importante: esta nueva tabla temporal conserva una relación con el modelo de datos. Entonces, a medida que SUMX itera sobre esta tabla, la transición de contexto obligará a que todo el modelo de datos se filtre en cada paso de la iteración de la tabla temporal.

Me gusta «imaginar» una nueva tabla transformándose en la parte superior del modelo de datos como se ilustra a continuación (esto no es real, solo una ilustración de lo que imagino). Encuentro útil imaginarlo así, ya que luego puedo imaginar los filtros fluyendo colina abajo desde las mesas en la parte superior hasta las mesas en la parte inferior.

image_thumb-17-6468468

CROSSJOIN siempre toma tablas como entradas. Por lo tanto, une 2 o más tablas para crear todas las combinaciones. Pero si desea crear una combinación de valores únicos en 2 columnas en 2 tablas diferentes, puede usar VALUES para eso (se muestra un ejemplo a continuación).

= SUMX(
  CROSSJOIN(VALUES(Table1[Column A]), VALUES(Table2[Column B])),
  [Measure 1] * [Measure 2]
)

Una solución aún mejor con SUMMARIZE

Finalmente, existe una solución mejor que la solución CROSSJOIN anterior: la mejor solución usa SUMMARIZE. La principal diferencia entre SUMMARIZE y CROSSJOIN (en este ejemplo de todos modos) es que SUMMARIZE solo devolverá filas para combinaciones válidas en el modelo de datos. Puse la función de tabla SUMMARIZE en DAX Studio y devolvió 24 filas (frente a 60 filas en CROSSJOIN).

image_thumb-18-5304514

Con este conocimiento, la fórmula más eficiente usando SUMMARIZE sería la siguiente, y SUMX solo necesitará iterar las combinaciones que realmente existen en la tabla de Reembolsos.

Total Rebate Payable =
     SUMX(
       SUMMARIZE(
          Rebates,
          Customers[Customer],
          Category[Category],
          Suppliers[Supplier],
          Division[Divison]
       ),
       [Total Sales] * CALCULATE(SUM(Rebates[Rate]))
    )

Esperamos que esto haya ayudado a desmitificar SUMX, CROSSJOIN y SUMMARIZE (al menos parcialmente) y haya ayudado a ampliar su comprensión de DAX.

!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ú.