Total acumulado en funcionamiento basado en el valor más alto

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

Contenidos

Nivel: avanzado

He ayudado a un par de personas en varios foros sobre este tema durante la última semana, y eso normalmente es una buena señal de que necesito una publicación de blog sobre el tema. El escenario es crear un total acumulado de Pareto basado en los principales productos, clientes o lo que sea. Esto es un poco más engañoso que un simple total acumulado hasta la fecha, ya que el «orden» de los mejores a los peores productos (o clientes o lo que sea) no se materializa en una tabla, ni tampoco las ventas totales. Esto significa que todos los cálculos deben realizarse sobre la marcha y eso puede complicarlo.

Total acumulado de los mejores productos a los peores

Mi ejemplo de hoy usa obras de aventura. Estoy viendo una lista ordenada de los mejores a los peores nombres de modelos de productos en función de las ventas totales y quiero calcular un total acumulado en ejecución utilizando DAX. La salida finalmente se verá así.

end-state-1571250

Alerta de spoiler: primero pruebe sus habilidades con DAX

La solución final a este problema parece relativamente sencilla y no utiliza DAX demasiado complejo. Cualquiera con habilidades intermedias de DAX sería más que capaz de entender cómo funciona. La razón por la que califiqué este tema como avanzado es porque es bastante difícil averiguar «cómo» hacerlo (Eso pensé de todos modos).

Si desea probar sus habilidades con DAX, pruébelo usted mismo antes de leer mi solución. Le prometo que parecerá fácil cuando vea la respuesta, pero una vez que vea la respuesta nunca sabrá si podría haberla resuelto usted mismo. Su objetivo es construir la tabla anterior usando DAX. Aquí hay un Libro de trabajo de Adventure Works en blanco puedes usar. Publique su experiencia en los comentarios a continuación si desea alguna pista o desea compartir lo que aprendió.

Como lo hice

Lo primero que hice fue crear una medida de ventas totales. Luego clasifiqué la tabla dinámica para poder ver los productos más vendidos en la parte superior.

image_thumb-4-3078043

Es una buena práctica configurar una tabla dinámica y resolver el problema paso a paso de esta manera. No intente resolver la paz mundial con una sola fórmula: divida el problema en pasos individuales manejables. ¡Hágase un favor y adopte este enfoque para todos sus problemas con DAX!

Lo siguiente que hice fue escribir una fórmula de rango simple de la siguiente manera.

= RANKX(ALL(Products[ModelName]),[Total Sales])

Una vez que se agregó este rango a mi tabla dinámica, cambié mi orden de clasificación para asegurarme de que la fórmula RANKX funciona independientemente del orden de los datos en la tabla dinámica; de hecho, lo hace. Esto no es esencial, por supuesto, pero es una buena tranquilidad.

sort_thumb-7545377

En esta etapa, realmente no sabía cómo escribir la fórmula, así que escribí esto siguiendo un patrón familiar de «toda la vida hasta la fecha». La vida útil hasta la fecha es un poco como «Año hasta la fecha», pero el punto de partida es la primera fecha de la historia en lugar de la primera fecha del año.

En esta medida, estoy usando [Ranked Models] en lugar de un campo de fecha.

image_thumb-5-9738719

La medida anterior simplemente me da las ventas totales de los 2 productos mejor clasificados. Por supuesto, funcionaría para cualquier otro número arbitrario que seleccione.

Y es fácil comprobar si funciona. Resalte algunas celdas concéntricas (1 a continuación), verifique el valor en la barra de información (2 a continuación) y compárelo con el resultado (3 a continuación).

image_thumb-6-9189686

Ahora todo lo que se requiere es reemplazar el número «2» con un cálculo dinámico que me dice cuál es el rango del producto actual en la fila de la tabla dinámica. Bueno, esto resultó ser mucho más difícil de lo que pensé al principio. Probé algunas cosas durante 10 minutos aproximadamente, pero no pude hacer que nada funcionara.

Tiempo de pensar

Me dirigí a encontrarme con un cliente y eso me dio un poco de tiempo para pensar. Es asombroso el impacto que tiene el “tiempo para pensar” en estas cosas. Creo firmemente que debes poner tus manos en el teclado y comenzar a escribir algo de DAX si quieres aprender. Pero también he aprendido que si te quedas atascado, simplemente debes alejarte y pensarlo detenidamente. Es sorprendente cuántas veces me despierto por la mañana (o en medio de la noche, desafortunadamente) con una respuesta a un problema que he tenido. tallarines. Si te quedas atascado en un problema, aléjate y piénsalo bien.

Top N al rescate

El problema al que me enfrentaba era que no podía pensar en una manera de reemplazar ese «2» codificado en mi intento anterior con el rango real de ese producto para la fila en la tabla dinámica. Claramente [Ranked Models] <= [Ranked Models (as in Pivot Table)] no iba a funcionar porque no hay ninguna forma (que se me ocurra) de acceder a ese valor. Supongo que podría haber usado VALUES () para extraer el nombre del modelo y de alguna manera recalcular el resultado de RANKX nuevamente usando eso, pero pensé que tenía que haber una manera más fácil. El problema real es que no tenía una tabla de productos clasificados en ningún otro lugar que no fuera el pivote: la tabla o los productos clasificados no se materializan en el modelo de datos, solo en la visualización. Lo que realmente necesitaba era una mesa virtual que contuviera todos los productos clasificados hasta el enésimo producto. Vaya, eso suena a TopN.

TopN es una función que devuelve una tabla de elementos clasificados según algún valor, en mi caso [Total Sales].

Las mesas virtuales son impresionantes

Antes de pasar a mi solución, permítanme hablarles sobre un concepto muy importante que la mayoría de los usuarios de DAX principiantes (y muchos más experimentados) no se dan cuenta. Todas las tablas virtuales creadas en DAX mantienen una relación de uno a muchos con la tabla desde donde se crearon. Lea esa oración nuevamente hasta que la tenga clara en su mente.

La implicación de esto es que puede usar una tabla temporal (1 a continuación) que crea con una fórmula DAX como VALUES, FILTER, TOPN, etc. y pasar esa tabla a CALCULATE. La tabla temporal conserva una relación con el resto del modelo de datos (2 a continuación) y, por lo tanto, cuando se produce la transición de contexto, todo el modelo de datos se filtra en función de esta tabla temporal. Si usa la Metodología de Diseño de Collie (como enseño en mi libro), sabrá que los filtros solo fluyen automáticamente «cuesta abajo», desde el lado 1 de la relación hasta el lado múltiple de la relación. La tabla que se muestra como 3 a continuación es una tabla virtual que no puede «ver», pero se comporta como si estuviera en su modelo de datos. Durante la transición de contexto, la Tabla 3 filtra la Tabla 4 y la Tabla 4 filtra la Tabla 5.

image_thumb-7-7084828

La imagen de arriba es solo una ilustración de lo que sucede detrás de escena: no puede ver la mesa virtual o la relación en absoluto, pero ayuda a visualizarla en su mente. Cubro este tema de tablas virtuales y muchos otros temas conceptuales fundamentales en mi libro. «Supercarga Excel».

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

Un total acumulativo dinámico de trabajo

Para completar mi solución a este problema, primero escribí la fórmula de TopN que planeaba usar para asegurarme de que funcionaba como esperaba. Debido a que TopN devuelve una tabla, no pude colocar la fórmula de TopN directamente en una tabla dinámica; tuve que envolverla dentro de COUNTROWS () de la siguiente manera:

Top N Products Count =
     COUNTROWS(
         TOPN([Ranked Models], ALL(Products[ModelName]), [Total Sales])
     )

En mi tabla dinámica, se veía así (que se muestra a continuación). La diferencia es que el [Ranked Models] La medida simplemente me da un valor escalar que me dice «qué rango» tiene el modelo de producto. El [Top N Products] medir es construir primero una tabla que contiene los N productos principales, y luego cuenta cuántos hay, dando el mismo resultado, por supuesto.

image_thumb-8-9896592

Una vez que pude ver que esta fórmula de TopN estaba devolviendo una tabla que estaba creciendo en función del Rango, pude usar la parte interna de esta fórmula como un parámetro de tabla dentro de un CALCULAR de la siguiente manera:

Cumulative Total based on Rank =
           CALCULATE([Total Sales],
                     TOPN([Ranked Models],ALL(Products[ModelName]),[Total Sales])
           )

Lo último que hice fue escribir un porcentaje de Pareto del total de la columna y colocarlo en mi tabla dinámica

% of Products Running Total =
         DIVIDE([Cumulative Total based on Rank],CALCULATE([Total Sales],ALL(Products)))

¡Bingo!

image-3-8232134

Si conoce una forma mejor o diferente de solucionar este problema. Siempre me gusta aprender, así que publique un comentario a continuación. Por supuesto, existen todos los problemas sobre cómo RANKX maneja los lazos frente a TOPN, etc., pero estoy seguro de que una vez que tenga este patrón en la mano, podrá resolver esos problemas usted mismo.

Actualización de Reader Solutions 24/8/16

Asegúrese de echar un vistazo a las soluciones publicadas por el lector en los comentarios a continuación. Todos podemos aprender de cómo otros resuelven problemas. Definitivamente he aprendido algunas cosas. La solución que quiero llamar vino de Jess (Oxenskiold)

cum-total-9789720

Esta es una solución tan simple. Déjame explicarte aquí cómo funciona. La declaración IF asegura que solo los Productos con Ventas se incluyan en el cálculo. El CALCULAR toma 2 entradas (en este ejemplo). El [Total Sales] en la línea 5 y el FILTRO en las líneas 6-10. La parte del FILTRO siempre se ejecuta primero. FILTER es un iterador. En este caso se iterará de una tabla de todos los modelos de producto (línea 7). La línea 8 es donde se realiza todo el trabajo. Toda la parte del filtro de la fórmula (líneas 6-10) opera en el contexto de fila actual de la tabla dinámica. Entonces, la primera mitad de la línea 8 mostrará la SUMA de lo que esté en la visualización (uno de los modelos de producto). La otra mitad de la línea 8 [Total Sales] es engañoso. la fórmula para [Total Sales] es simplemente SUM (Ventas[SalesAmount]), por lo que parece que la fila 8 dice SUM (Ventas[SalesAmount]) <= SUM (Ventas[SalesAmount]). La razón por la que funciona es que [Total Sales] es REALMENTE equivalente a CALCULATE (SUM (Sales[SalesAmount])). Cada medida tiene un CÁLCULO implícito envuelto alrededor que no puede ver. Debido a este CALCULAR, se produce la transición de contexto y el modelo de datos se filtra para el modelo de producto actual iterado por FILTER.

Los aprendizajes clave para mí después de ver esta solución son

  1. No siempre obtienes la mejor (o más simple) solución la primera vez
  2. A veces hay otra solución que te está mirando a la cara. En este caso [Total Sales] es un proxy perfecto para la clasificación del producto.

También me gustan mucho las soluciones de Owen. Son muy similares al de Jess en el sentido de que utilizan las ventas totales como un indicador del rango, algo que pasé por alto por completo.

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