Ingresos por año solo para clientes habituales

Datos

Tengo datos que muestran las ventas por cliente por año. Estos datos también incluyen a qué empresa pertenecen las ventas de los clientes. El mismo cliente puede comprar en diferentesempresas ent. Aquí hay una muestra de los datos en formato dinámico (la columna de repetición no es parte de los datos):

Resultado final deseado
Necesito crear una tabla que muestre los Ingresos por año, para cada empresa vendedora, por solo clientes habituales (ver definición abajo).

Definición de cliente habitual

Un cliente habitual es cualquier cliente que, durante un período de tiempo específico (para este ejemplo, de 2012 a 2018), realizó una compra en 3 o más años con el misma empresa vendedora. Si un cliente realizó una compra en 2012 y 2013 con el Vendedor A y en 2014 con el Vendedor B, este no es un cliente habitual para ninguno de los vendedores. Si un cliente realizó una compra en 3 años o más solo con el Vendedor A, solo se considera un cliente habitual para el Vendedor A, y ningún otro vendedor.

Otra información

– Esto se ejecutará en millones de filas de datos en un DirectQuery a una base de datos del servidor SQL

– El servidor SQL no tiene una tabla de fechas

Primer intento

Probé la siguiente medida en una muestra más pequeña de datos (similar a la muestra anterior) y parece estar haciendo lo que quiero que haga, pero es demasiado ineficiente para ejecutarse en un conjunto de datos más grande.

Sales for repeat customers = 
VAR Table1 =
    FILTER (
        SUMMARIZE (
            CALCULATETABLE ( Data, ALL ( Data[Year] ) ),
            Data[Seller],
            Data[Customer],
            Data[Year],
            "Sales > 0", [Sales]
        ),
        [Sales > 0] > 0
    )
VAR RepeatFlag =
    SWITCH ( TRUE (), COUNTROWS ( Table1 ) >= 3, 1, 0 )
RETURN
[Sales] * RepeatFlag

Gracias de antemano por cualquier ayuda que pueda brindar !!!!

Así que esto fue mucho más complicado de lo que esperaba al principio. Me las arreglé para obtenerlo en unos pocos pasos, que enumeraré y luego veré si puedo combinarlo todo en una sola consulta.

El primer truco fue descubrir exactamente cómo dividir todo correctamente. Lo primero que hice fue recrear algunos de sus datos. Aquí está la tabla que usé para mis pruebas:
VendedorCompradorAñoCantidad

A	1	2013	683250
A	1	2014	5485
B	1	2012	106071
B	1	2013	102168
B	1	2014	225458
B	1	2015	361312
B	1	2016	130487
B	1	2017	116585
B	14	2013	8985
B	13	2014	2342
B	13	2017	34534
B	5	2012	12927
B	5	2013	23312
B	5	2014	20166
B	5	2015	25663
B	5	2016	43895
B	5	2017	49
B	5	2018	1603

Utilicé todos los datos del vendedor B para poder probar a los compradores que eran repetidores y los que no estaban dentro del mismo vendedor, y puse en la primera fila los datos del vendedor A para asegurarme de que no estaba mezclando los vendedores A y B.

Lo siguiente que hice fue crear una columna calculada en esta tabla, que devuelve un valor booleano en función de si era un cliente habitual. Hice esto aquí para separar esta lógica de la eventual tabla de resumen y solo tratar un problema a la vez. Aquí está el DAX para eso:

RepeatCustBool = 
IF(
 COUNTROWS(
     FILTER(SellingData, 
         SellingData[Buyer] = EARLIER(SellingData[Buyer]) 
         && SellingData[Seller] = EARLIER(SellingData[Seller])  )
     ) >= 3, 
TRUE, FALSE)

Esto me dio una manera rápida y fácil de hacer referencia si cualquier combinación de Comprador/Vendedor contaba como negocio repetido.

Lo siguiente fue crear otra tabla de vendedor y año, pero solo donde el booleano de cliente repetido era verdadero. Fui a Modelado > Crear tabla y usé esta consulta DAX para crear una nueva tabla:

Summary = SUMMARIZECOLUMNS(SellingData[Seller],SellingData[Year],FILTER(SellingData,[RepeatCustBool]=TRUE()),"Repeat Revenue",SUM(SellingData[Amount]))

Luego, por curiosidad, hice lo mismo con SUMMARIZE, porque no conozco la diferencia de rendimiento entre los dos:

Summary2 = SUMMARIZE(FILTER(SellingData,[RepeatCustBool]=TRUE()), SellingData[Seller],SellingData[Year],"Repeat Revenue",SUM(SellingData[Amount]))

De todos modos, eso da una tabla con esta información:

Ingresos repetidos del año del vendedor

B	2012	118998
B	2013	125480
B	2014	245624
B	2015	386975
B	2016	174382
B	2017	116634
B	2018	1603

¡Lo que coincide con la fila B en su matriz de resultados deseada! ¡Excelente!

Ahora llegamos al rendimiento. Con millones de filas, no desea tener que crear una nueva columna y evaluarla para cada entrada solo para el booleano de cliente repetido (a menos que lo esté usando en otro lugar). Después de investigar un poco y terminar aquí, parece que la mejor manera de crear esta tabla para el rendimiento mientras se filtra es con SUMMARIZECOLUMNS. Aquí está el DAX para crear la tabla de resumen en un solo paso sin una columna calculada:

Summary3 = SUMMARIZECOLUMNS(SellingData[Seller],SellingData[Year],FILTER(SellingData,COUNTROWS(FILTER(SellingData,SellingData[Buyer]=EARLIER(SellingData[Buyer])&&SellingData[Seller]=EARLIER(SellingData[Seller])))>=3),"Repeat Revenue",SUM(SellingData[Amount]))

Esto parece que podría ser una pérdida de rendimiento para mí, ya que tiene un FILTRO anidado, pero parece que no puedo encontrar una manera de hacerlo todo con un comando de filtro. Puede haber alguna forma de hacerlo con GROUPBY en lugar de SUMMARIZE, pero no lo veo.

Estaría interesado en el rendimiento de estas opciones en su conjunto de datos masivo, y podemos trabajar para refinar la opción que funcione mejor para usted.

Seguí jugando con esto y obtuve esta consulta GROUPBY para crear la misma tabla. Avíseme si esto tiene un mejor rendimiento que la versión SUMMARIZECOLUMNS:

SUMMARY 4 = GROUPBY(FILTER(SellingData,COUNTROWS(FILTER(SellingData,SellingData[Buyer]=EARLIER(SellingData[Buyer])&&SellingData[Seller]=EARLIER(SellingData[Seller])))>=3),SellingData[Seller],SellingData[Year],"Repeat Revenue",SUMX(CURRENTGROUP(),SellingData[Amount]))

Estoy trabajando principalmente para averiguar si hay alguna forma de eliminar uno de los filtros o reducir el conjunto de datos que está filtrando a través de CROSSJOIN o algo así.

cmmahan,

Recibo el siguiente error para las medidas Resumen 3 y Resumen 4: «La expresión hace referencia a varias columnas. Varias columnas no se pueden convertir en un valor escalar».

Seguiré investigando para ver si esto es solo el resultado de que no traduje las medidas correctamente en mi informe.

EDITAR – No importa … Obviamente debería haber hecho de esto una nueva tabla en lugar de una medida. Desafortunadamente, el nuevo problema tanto para ‘Summary3’ como para ‘Summary4’ es que el «conjunto de resultados de una consulta a una fuente de datos externa ha excedido el tamaño máximo permitido de ‘1000000’ filas».

mmm… si. Estaba preocupado por eso.

Supongo que el FILTRO anidado está causando el problema. Simplemente no veo una manera de aplicar el filtro «coincidir con el vendedor/año», obtener un recuento de los montos coincidentes y luego filtrar si lo usa en función de eso.

Para evitar esto, ¿sería malo para sus datos o no sería deseable usar la primera columna calculada que mencioné y agregar la columna Cliente habitual a sus datos? Si lo hace, puede crear la tabla de resumen comprobando solo ese valor booleano en lugar de hacer que DAX cree varias tablas virtuales mientras crea este valor booleano sobre la marcha.

RepeatCustBool = IF( COUNTROWS( FILTER(SellingData, SellingData[Buyer] =EARLIER(SellingData[Buyer]) && SellingData[Seller] = EARLIER(SellingData[Seller])  ) ) >= 3, TRUE, FALSE)

Entonces debería poder crear una tabla de resumen usando este código:

Summary 6= SUMMARIZECOLUMNS(SellingData[Seller],SellingData[Year],FILTER(SellingData,[RepeatCustBool]=TRUE()),"Repeat Revenue",SUM(SellingData[Amount]))

EDITAR:
Además, la versión GROUPBY para completar, ya que todavía no estoy seguro de SUMMARIZECOLUMNS vs GROUPBY:

Summary 7 = GROUPBY(FILTER(SellingData,[RepeatCustBool]),SellingData[Seller],SellingData[Year],"Repeat Revenue",SUMX(CURRENTGROUP(),SellingData[Amount]))

Desafortunadamente, la función CountRows no está permitida como parte de una columna calculada en los modelos de DirectQuery. ¡DirectQuery me ha hecho la vida más difícil!

Por ahora, me aseguraré de documentar este requisito con la mayor claridad posible con mi equipo y luego seguiré trabajando en ello. Lo último que quiero hacer es finalmente encontrar una solución y luego agregar otra variable a la mezcla. Me aseguraré de publicar cualquier actualización o progreso que haga mientras tanto.

¡De nuevo, gracias por tu ayuda!

Sí, eso suena como un oso de un problema. La siguiente solución que propondría es pedirle a quien administre sus datos que agregue la columna de clientes repetidos. Si ya está en los datos, ¡no es necesario que lo cree en Power BI!

Buena suerte en el proyecto.

Entradas relacionadas

Deja un comentario Cancelar respuesta