Evite que la medida calcule para cada elemento en Power Pivot Chart

Un usuario Pregunto ✅

GuillermoPH2

Soy nuevo en DAX y lo estoy usando en Power Pivot en Excel.

Mi problema es (¡con suerte!) uno simple: tengo una medida que realmente hace lo que quiero que haga, un gran logro dada mi lenta curva de aprendizaje de DAX. Pero cuando lo lanzo a mi gráfico dinámico y agrego campos adicionales de mis listas maestras al Filas área, calcula cada subtítulo para cada artículo. Debido a que mis listas Maestras son necesariamente grandes, esto arroja una tabla dinámica de más de 1,000,000 de líneas, que ni Excel ni yo podemos manejar….!

Este problema amenaza con arruinar un hermoso momento en mi desarrollo de Daxhombre frustrado

Sé cómo filtrar ceros dentro de la tabla dinámica, pero esto no es deseable, ya que algunos datos necesarios a veces son cero. Necesito una forma de filtrar tan solo PO activas, clientes y proveedores (los que tienen datos en el Ventas tabla o la compras tabla) aparecen. He perdido el tiempo, pero estoy conduciendo a ciegas. Ni siquiera sé si lo que estoy preguntando es posible.

Tengo un conjunto completo de datos de muestra si eso es útil. Muestro la medida debajo de eso.

Tabla de compras:

Fecha de compra Nombre OC Proveedor Monto de compra

01/01/2018 Sam1 PO1 americano 200.00
18/01/2018 sam2 PO2 americano 300.00
02/04/2018 Sam3 PO3 Unido 500.00
21/02/2018 Sam4 Unido 40.00
10/03/2018 Sam5 americano 50.00
27/03/2018 Sam5 PO6 Sur oeste -20.00

Tabla de ventas

Fecha de venta Nombre OC Proveedor Cantidad de venta

02/03/18 Sam1 PO1 americano 400
01/07/18 Sam25 PO17 Unido 600
08/05/18 Sam3 PO3 americano 1000
09/05/18 Sam5 PO7 Sur oeste 200
29/03/18 Sam25 PO2 americano 600

Maestro de nombres

Sam1
sam2
Sam3
Sam4
Sam5
Sam6
sam7
Sam8
sam9
Sam10
sam11
sam12
Sam13
Sam14
Sam15
Sam16
Sam17
Sam18
Sam19
Sam20
Sam21
Sam22
Sam23
Sam24
Sam25
Sam26
Sam27
Sam28
Sam29

Maestro de orden de compra

PO1
PO2
PO3
PO4
PO5
PO6
PO7
PO8
PO9
PO10
PO11
PO12
PO13
PO14
PO15
PO16
PO17
PO18
PO19
PO20
PO21
PO22
PO23
PO24
PO25
PO26
PO27
PO28
PO29

Maestro de proveedores

americano
Unido
Sur oeste

Debido a otras tablas en el conjunto de datos real, las listas maestras son mucho más grandes (como en este ejemplo) que los valores únicos en el Ventas y compras mesas.

Aquí está mi medida:

Inventory:=CALCULATE
        (IF
            (SUMX(Sales,Sales[Sale Amount])<SUMX(Purchases,[Purchase Amount]),
            SUMX(Purchases,[Purchase Amount])
            ,0),
                   FILTER
                          (ALL('Calendar'[Date]),
                           'Calendar'[Date]<MAX('Calendar'[Date])))

Como dije, hace lo que quiero. Este copiar/pegar muestra cómo se ve la tabla dinámica con solo el Nombre lista maestra. (Ambos Ventas y Compra las tablas están vinculadas a todas las listas maestras).

Inventario Ene Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic Grand Total

Sam1 200 0 0 0 0 0 0 0 0 0 0 0 0
Sam10 0 0 0 0 0 0 0 0 0 0 0 0 0
sam11 0 0 0 0 0 0 0 0 0 0 0 0 0
sam12 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam13 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam14 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam15 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam16 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam17 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam18 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam19 0 0 0 0 0 0 0 0 0 0 0 0 0
sam2 300 300 300 300 300 300 300 300 300 300 300 300 300
Sam20 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam21 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam22 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam23 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam24 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam25 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam26 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam27 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam28 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam29 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam3 0 500 500 500 0 0 0 0 0 0 0 0 0
Sam4 0 40 40 40 40 40 40 40 40 40 40 40 40
Sam5 0 0 30 30 0 0 0 0 0 0 0 0 0
Sam6 0 0 0 0 0 0 0 0 0 0 0 0 0
sam7 0 0 0 0 0 0 0 0 0 0 0 0 0
Sam8 0 0 0 0 0 0 0 0 0 0 0 0 0
sam9 0 0 0 0 0 0 0 0 0 0 0 0 0

Puede imaginar lo peludo que se pone cuando agrego las órdenes de compra o los proveedores.

Cualquier ayuda es muy apreciada.

Guillermo

En respuesta a GuillermoPH2

Hola Guillermo,

Adjunté el archivo pbix en el archivo adjunto. Ahora podemos hablar en base a los mismos datos.

Por favor, pruebe este. ¿Cómo debemos manejar los valores negativos? Se calcula como 0 en la siguiente fórmula.

Inventory 3 =
VAR temp =
    CALCULATE (
        IF (
            SUMX ( Sales, IF ( Sales[Sale Amount] < 0, 0, 'Sales'[Sale Amount] ) )
                <= SUM ( Purchases[Purchase Amount] ),
            SUM ( Purchases[Purchase Amount] ),
            0
        ),
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < MAX ( 'Calendar'[Date] ) )
    )
RETURN
    IF (
        HASONEVALUE ( Purchases[Name] ) || HASONEVALUE ( Sales[Name] )
            || ISBLANK ( temp ) = FALSE (),
        temp,
        BLANK ()
    )

Parece que PO no hace nada aquí. ¿Deberíamos considerarlo?

¿Cuál podría ser la influencia del PO idéntico?

Si puede publicar el resultado esperado, eso facilitaría las cosas.

detener_medir

Atentamente,

Valle

greg_deckler

Los datos de muestra serían de gran ayuda. Consulte esta publicación sobre cómo obtener una respuesta rápida a su pregunta: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

GuillermoPH2

En respuesta a greg_deckler

Gracias por el consejo. Trabajaré para lograrlo, aunque será mañana antes de que pueda reunir un buen conjunto de datos.

Guillermo

En respuesta a GuillermoPH2

Hola Guillermo,

¿Que tal este?

Inventory 3 =
IF (
    HASONEVALUE ( Purchases[Name] ) || HASONEVALUE ( Sales[Name] ),
    CALCULATE (
        IF (
            SUM ( Sales[Sale Amount] ) <= SUM ( Purchases[Purchase Amount] ),
            SUM ( Purchases[Purchase Amount] ),
            0
        ),
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < MAX ( 'Calendar'[Date] ) )
    ),
    BLANK ()
)

When_using_Calculate_in_DAX_Pivot_Chart_displays_all_values_in_otherwise_filtered_P

Atentamente,

Valle

GuillermoPH2

En respuesta a v-jiascu-msft

Dale, muchas gracias por tomarte el tiempo de responder a esto. Nunca usé HASONEVALUE antes. Me gusta mucho cómo limpió las cosas.

El problema es que el inventario debe mostrarse para cada mes. hasta que se haga una venta. Por ejemplo, Sam3 necesita mostrar $500 para febrero, marzo y abril, y $0 para el resto.

¿Alguna forma de agregarlo a la mezcla?

Gracias de nuevo por tomarse el tiempo para esto!

Guillermo

La otra dificultad con los datos es que algunas órdenes de compra se aplican a más de un cliente. No pude mostrar eso en mis datos de muestra…

En respuesta a GuillermoPH2

Hola William,

¿Que tal este?

Inventory 2 =
VAR temp =
    CALCULATE (
        IF (
            SUM ( Sales[Sale Amount] ) <= SUM ( Purchases[Purchase Amount] ),
            SUM ( Purchases[Purchase Amount] ),
            0
        ),
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < MAX ( 'Calendar'[Date] ) )
    )
RETURN
    IF (
        HASONEVALUE ( Purchases[Name] ) || HASONEVALUE ( Sales[Name] )
            || ISBLANK ( temp ) = FALSE (),
        temp,
        BLANK ()
    )

When_using_Calculate_in_DAX_Pivot_Chart_displays_all_values_in_otherwise_filtered_P2

Atentamente,

Valle

GuillermoPH2

En respuesta a v-jiascu-msft

¡Guau! Muchas gracias por dar una solución de trabajo. Necesito aprender más sobre HASONEVALUE y VAR…

Tuve un problema técnico (no quiero ser desagradecido, son simplemente mis datos extraños): si el Ventas la tabla se parece a esto

Fecha de venta Nombre Cantidad de venta del proveedor de la orden de compra

02/03/18 Sam1 PO1 americano 400
01/07/18 Sam25 PO17 Unido 600
29/03/18 Sam25 PO2 americano 600
01/03/18 Sam3 PO10 Unido -700
02/05/18 Sam3 PO3 Unido 1000
09/05/18 Sam5 PO7 Sur oeste 200
10/02/18 Sam4 PO1 americano -300

(El orden se mezcló con la muestra anterior, pero esencialmente agregué un negativo a Sam3)

((También agregué a Sam4, con un PO idéntico al de Sam1, porque eso sucede a veces))

Lo que sucede cuando haces Pivot es que Sam3 muestra el inventario para el resto del año. También el Las subfilas de orden de compra bajo su proveedor no aparecen. Es muy frustrante. (No puedo pegar mi tabla dinámica por alguna razón para mostrar resultados).

¿Habría alguna forma de filtrar el cálculo? primero por cliente, luego por orden de compra? Si hiciera eso, donde solo mirara los totales de un determinado cliente y luego una determinada orden de compra, creo que funcionaría.

No fue una gran falla: creo que solo un cliente de 300 tuvo el problema. Pero sería más que asombroso si pudiera resolverse…

Muchas gracias por sus respuestas pacientes.

Guillermo

PD: si mi solicitud no es realmente factible, házmelo saber y marcaré tu publicación más reciente como una solución, porque realmente funcionó en general.

En respuesta a GuillermoPH2

Hola Guillermo,

Adjunté el archivo pbix en el archivo adjunto. Ahora podemos hablar en base a los mismos datos.

Por favor, pruebe este. ¿Cómo debemos manejar los valores negativos? Se calcula como 0 en la siguiente fórmula.

Inventory 3 =
VAR temp =
    CALCULATE (
        IF (
            SUMX ( Sales, IF ( Sales[Sale Amount] < 0, 0, 'Sales'[Sale Amount] ) )
                <= SUM ( Purchases[Purchase Amount] ),
            SUM ( Purchases[Purchase Amount] ),
            0
        ),
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < MAX ( 'Calendar'[Date] ) )
    )
RETURN
    IF (
        HASONEVALUE ( Purchases[Name] ) || HASONEVALUE ( Sales[Name] )
            || ISBLANK ( temp ) = FALSE (),
        temp,
        BLANK ()
    )

Parece que PO no hace nada aquí. ¿Deberíamos considerarlo?

¿Cuál podría ser la influencia del PO idéntico?

Si puede publicar el resultado esperado, eso facilitaría las cosas.

detener_medir

Atentamente,

Valle

GuillermoPH2

En respuesta a v-jiascu-msft

Dale, acabo de ejecutar la nueva fórmula que diste, ¡y parece funcionar!

Me has ayudado tremendamente; Creo que mis datos reales tienen algunos errores que tendré que descubrir, pero por ahora estoy muy contento. Muchas gracias por prestar su experiencia.

Espero que mi primera respuesta de hoy no te haya hecho perder demasiado tiempo para leer. Pensé que debería explicar lo que necesitaba, pero debería haber probado la nueva medida que diste primero…

Si tiene comentarios sobre algo que podría ser útil para mis problemas aquí, agradecería sus comentarios. De lo contrario, ¡que tengas una excelente semana y gracias de nuevo!

Guillermo

GuillermoPH2

En respuesta a v-jiascu-msft

¡Muchas gracias por seguir conmigo en esto! Realmente está haciendo mi semana.

Cambio importante: para ver el glitch del que hablo, en el Ventas necesita cambiar Sam3 (PO3, por $ 1,000) a una fecha en febrero, como 2/15 (en lugar de la fecha actual 5/8/18)

El problema surge cuando agrega los campos PO y Proveedor al área de filas del gráfico dinámico. Necesito esa información, porque cuando nos desplazamos hacia abajo en el gráfico, debemos poder ver todo eso de un vistazo.

Como dije, estoy usando Excel y, de hecho, acabo de instalar el escritorio de Power BI esta mañana, así que no sé cómo funciona esto en BI. ¿Puede agregar filas (como orden de compra y proveedor) y verlas como subtítulos debajo del cliente? Así es como funciona Pivot en Excel, y solo cuando agrega las subfilas de Proveedor y Orden de compra debajo del cliente, surge el problema.

No puedo encontrar la manera de publicar capturas de pantalla aquíhombre frustrado, y mi tabla dinámica no copia/pega por algún motivo. Debe tener un aspecto como este:

Sam1

americano

PO1 200 (ene)

sam2

americano

PO2 300 (enero-diciembre)

Sam3

En realidad, no debería mostrar inventario para ningún mes. Pero mi pivote, cuando entro en los campos Proveedor y OC, ¡lo muestra con un inventario de $ 500 para febrero-diciembre!

De alguna manera está combinando la venta negativa (nota de crédito) de -$700 de PO10 con la venta positiva de $1,000 en PO3 y usando el neto de $300 para mostrar que PO3 aún tiene inventario. En los datos reales, parece que solo hay clientes con ventas negativas (incluso si la venta negativa es para una orden de compra diferente). [for that customer]) que la medida la combina con las ventas de la orden de compra actual y luego muestra el inventario para la compra porque la venta parece no estar allí (o ser demasiado pequeña).

Es extraño, pero en la muestra, si cambia Sam3, (PO3, $ 1,000) a una fecha de mayo, mi medida de tabla dinámica calcula el inventario perfectamente. ¿Algo sobre tener la venta en el mismo mes que la compra hace que se agregue la venta negativa del mes anterior?

No entiendo todo esto, pero creo que lo que necesito es que la fórmula mire primero al cliente y luego en cada PO único individualmente. Me gustaría ejecutar todo fuera de la orden de compra, pero algunos clientes tienen el mismo número de orden de compra, por lo que tiene que ir «Mirar al cliente» –> «Mirar la orden de compra». Tal como está ahora, funciona muy bien mirando a los clientes, pero si un Cliente tiene dos PO, parece combinar sus totales, solo en el caso de ventas negativas.

Lo sé, esto es una especie de lío.hombre triste. Todavía no he tenido tiempo de probar tu nueva medida, pero lo haré en cuanto pueda. Solo pensé en tratar de responder y explicar mi situación más a fondo.

Muchas gracias por tu ayuda.

Guillermo

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *