cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LasseL
Helper I
Helper I

Calcular un enfoque eficaz; sumx, resumir, suma - caso de datos de ventas transaccionales y exch.

Querida comunidad,

Problema principal; encontrar el enfoque más eficaz en computación.

Modelo de datos actual;

LasseL_0-1603097456015.png

Información de fondo;

  • Ingresos y coste de ventas son datos transaccionales, cada línea que representa información sobre las ventas reales en una fecha determinada.
  • Las líneas de ingresos por ventas están en monedas diferentes.
  • La tabla tiene aproximadamente 11 millones de líneas.
  • Los tipos de cambio contienen información sobre los tipos de cambio de divisas desde y hacia la moneda en fechas particulares. La fecha es una "válida a partir de la fecha" y no es coherente en términos de un tipo de cambio para todas las fechas en Ingresos y costos de ventas (más comúnmente solo la última fecha de cada mes).

Tarea;

  1. Establecer un informe consolidado en una moneda consolidada por elección del usuario.
  2. Utilice la última divisa válida "antes" Ingresos de ventas y costo" Fecha.

Mi primer acercamiento;

  • Solución; Uso de Power Query con Table.SelectRows para agregar columnas a Sales Revenue... con las monedas de notificación seleccionadas y el importe calculado en función de eso. Es decir, realizar la transformación en el momento de cargar los datos.

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Max(Table.SelectRows(Rates, (Rate) => Rate[Date]<=[Date] and Rate[Currency]=[Currency]),"Date")
),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Rate"}, {"Rate"})​

  • Problema; con una carga limpia los servicios de Power BI podían cargar alrededor de 100k líneas por segundo, con la solución anterior se redujo a solo 10 líneas por segundo y con 11 millones de líneas tardaría días en terminarse.

Segundo enfoque;

  • Solución; Uso de DAX para la transformación a través de SUMX puro (Ingresos de Ventas...) con condiciones de cálculo para la conversión

Sales Revenue = 
SUMX(
    'Sales Revenue and Cost',
    'Sales Revenue and Cost'[Line Amount] * 
    Calculate(
        min('Exchange Rates'[Rate]),
        filter('Exchange Rates', 
        'Exchange Rates'[From Currency]='Sales Revenue and Cost'[Company Currency] && 
        'Exchange Rates'[Date]<='Sales Revenue and Cost'[Date] &&
        'Exchange Rates'[To Currency]=SELECTEDVALUE('Reporting Currency'[Currency])
        )   
    )
)​

  • Problema; Soluciones proporciona la respuesta perfecta y la mejor característica de ser dinámicamente capaz de seleccionar "otra" moneda de notificación, sin embargo, publicar esto en los servicios es evidente que es muy ineficaz, lento y a veces los servicios de bi de energía post un error que indica demasiado consumo de memoria - es decir, no es una solución "con capacidad de producción".

Tercer enfoque, y donde estoy atascado:

  • Solución; He leído que SUMX no se va a usar como lo hice causando hace un cálculo para cada fila cada vez, y que la gente ha venido alrededor de ella mediante el uso de SUMX VALUES, resumen o una combinación. Estoy en el punto de probar SUMX VALUES, pero no puedo conseguir que se reproduzca correctamente y me pregunto si es la solución correcta en absoluto, 2 medidas combinadas con "Ingresos de Ventas";
  • Ingresos por Ventas : 
    SUMX(
        VALUES('Sales Revenue and Cost'[Currency]),
        [Sales Revenue LCY] * [Sales Revenue Reporting Currency Rate]
    )
    
    Ingresos de Ventas LCY - SUM('Ingresos de Ventas y Costo'[Importe de Línea]) Tasa de Moneda de Reporte de
    
    Ingresos de Ventas ? 
    Calculate(
            min('Tipo de Cambio'[Tasa]),
            filtro('Tipos de Cambio', 
            'Tipos de Cambio'[De la moneda]-MAX('Ingresos y costo de ventas'[Moneda]) && 
            'Tipos de cambio'[Fecha]<'MAX('Ingresos y costo de ventas'[Fecha]) &&
            'Tipos de cambio'[A moneda]-SELECTEDVALUE('Reporting Currency'[Currency])
            )   
        )
  • También me preocupa si el método asegura que estoy utilizando el tipo de cambio de divisa correcto "antes" de la fecha de contabilización de ingresos de ventas - como mínimo me gustaría que un nivel mensual de cantidad resumida se traduce por el tipo de cambio "antes".

Estoy atascado, nivel bastante principiantes y he pasado tantas horas hasta el punto de mucha frustración - cualquier indicio de la dirección correcta sería muy apreciado!

Saludos

Salir

11 REPLIES 11
LasseL
Helper I
Helper I

Continué un poco por mi cuenta.

Ensayo actual;

Sales Revenue = 
VAR ExchangeRate =  Calculate(
                        min('Exchange Rates'[Rate]),
                        filter('Exchange Rates', 
                        'Exchange Rates'[From Currency]=MIN('Sales Revenue and Cost'[Currency]) && 
                        'Exchange Rates'[Date]<=MAX('Sales Revenue and Cost'[Date]) &&
                        'Exchange Rates'[To Currency]=SELECTEDVALUE('Reporting Currency'[Currency])
                        )   
                    )

RETURN

SUMX(
    SUMMARIZE('Sales Revenue and Cost','Sales Revenue and Cost'[Currency]),
    [Sales Revenue LCY] * ExchangeRate
)

Con [Ingresos de Ventas LCY];

Sales Revenue LCY = SUM('Sales Revenue and Cost'[Line Amount])

Parece funcionar mejor computar y memorizar en cuanto, y un objeto visual con año-mes calcula el importe correcto de la divisa de informes, pero suma las columnas incorrectamente con la suma total de LCY veces "último tipo de cambio"...

Y todavía tengo mis dudas si esta es la mejor práctica en todo 😞

v-yingjl
Community Support
Community Support

Hola @LasseL ,

Según su descripción, la versión de prueba actual tendrá un mejor rendimiento porque utiliza variable y summazire una tabla en la función sumx().

"pero suma incorrectamente las columnas con la suma total de LCY veces "último tipo de cambio""

¿Cuál es su salida esperada? ¿Podría considerar compartir más detalles como archivos de muestra, capturas de pantalla, etc. sobre este tema para una discusión más detallada?

Los datos de muestra y la salida esperada ayudarían enormemente.
Por favor, consulte esta publicación sobre cómo obtener respuesta a su pregunta rápidamente:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Saludos

Yingjie Li

Querido Yingjie,

Muchas gracias por volver.

Por razones confidenciales no puedo compartir el PBIX real y los datos en los que estoy trabajando, pero traté de reproducir un conjunto de datos de demostración (excel) y un fil PBIX con una configuración similar.

Adjunto;

Lo que esperaría es que los totales de Ventas sean correctos con el último tipo de cambio al elegir DKK o EUR como moneda de notificación;

Screenshot 2020-10-22 134815.png

¿Esto ayuda?

v-yingjl
Community Support
Community Support

Hola @LasseL ,

Parece que no pude descargar la muestra debido al acceso. Tal vez pueda modificar el acceso a la visita y considerar volver a compartir el archivo de ejemplo.

Saludos

Yingjie Li

Hola de nuevo Yingjie,

Mis más sinceras atologías, voy a probar otro medio; https://we.tl/t-264shFE8H6

¿Funciona?

Saludos

Salir

v-yingjl
Community Support
Community Support

Hola @LasseL ,

¿Así que tu resultado esperado es así?

cy.png

Si es así, intente modificar así:

Sales Revenue =
VAR ExchangeRate =
    CALCULATE (
        MIN ( Rates[Rate] ),
        FILTER (
            Rates,
            Rates[From Currency] = MIN ( Orders[Currency] )
                && Rates[Date] <= MAX ( Orders[Date] )
                && Rates[To Currency] IN DISTINCT ( 'ReportingCur'[Currency] )
        )
    )
RETURN
    SUMX (
        SUMMARIZE ( Orders, Orders[Currency] ),
        [Sales Revenue LCY] * ExchangeRate
    )

Best Looks,
Yingjie Li

Si este post ayuda, por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Wow, lo que una respuesta rápida, y lo que un pequeño correcto con gran impacto!

Muchas gracias, ayudó mucho.

Si aún así sigues teniendo problemas, visita la página de ayuda de Firefox.

1) Parece que no es consistente en elegir el último tipo de cambio válido de "antes" la fecha de pedido, a veces sí, un par de veces que salta?

LasseL_1-1603435174133.png

2) y más importante, si cambio la moneda de los informes de EUR a DKK obtengo algunos resúmenes extraños;

LasseL_2-1603435399082.png

Cualquier idea sobre por qué, ¿qué hizo el IN DISTINCT exactamente 🙂

v-yingjl
Community Support
Community Support

Hola @LasseL ,

Me parece que ha deshabilitado las interacciones entre la segmentación de datos y las dos tablas debajo del archivo de ejemplo para que el valor se vea como "extraño", simplemente reanude la interacción y el valor shoule sea normal.

interaction.png

Acerca de la instrucción IN, creates una condición OR lógica entre cada fila que se compara con una tabla y la instrucción Distinct quita los valores de dupulicate y solo devuelve valores únicos.

Puede consultar estos artículos si está interesado en ellos:

  1. El operador IN en DAX
  2. Uso del operador "IN" en DAX
  3. DISTINCT (columna)

Adjunto el archivo de muestra modificado en el siguiente, espera ayudarle.

Best Looks,
Yingjie Li

Si este post ayuda, por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Muchas gracias por la corrección de filtros, la explicación y la definición!

¿Cómo es que la suma del valor de ventas no refleja la suma real de cada línea, en este caso la suma del DKK debe ser de 14 + 311 - 325DKK

LasseL_0-1603452603233.png

v-yingjl
Community Support
Community Support

Hola @LasseL ,

Reescribo la medida de la siguiente manera:

Sales Revenue = 
VAR tab =
    SUMMARIZE (
        'Orders',
        'Calendar'[Date],
        'Orders'[Currency],
        'Orders'[Price],
        "REp",
            CALCULATE (
                MIN ( Rates[Rate] ),
                FILTER (
                    Rates,
                    Rates[From Currency] IN DISTINCT ( Rates[From Currency] )
                        && 'Rates'[To Currency] IN DISTINCT ( ReportingCur[Currency] )
                )
            )
    )
RETURN
    SUMX ( tab, [REp] * [Price] )

Ahora el valor de suma debe ser correcto:

sum.png

Vea el archivo de ejemplo en la siguiente, espera ayudarle.

Best Looks,
Yingjie Li

Si este post ayuda, por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Hola de nuevo @v-yingjl ,

En el lado positivo, sí, ahora da un cálculo (casi) perfecto (falta la mirada correcta original del tipo de cambio más cercano (antes) a la fecha de transacción - pero puedo arreglar eso.

En el lado negativo, entonces creo que estamos de vuelta al mal rendimiento de la solución original de un cálculo línea por línea (SUMX) que fue el enfoque que resultó ineficaz desde el principio cuando se trabaja en el gran conjunto de datos, es decir, la medida original fue;

Sales Revenue = 
SUMX(
    'Sales Revenue and Cost',
    'Sales Revenue and Cost'[Line Amount] * 
    Calculate(
        min('Exchange Rates'[Rate]),
        filter('Exchange Rates', 
        'Exchange Rates'[From Currency]='Sales Revenue and Cost'[Company Currency] && 
        'Exchange Rates'[Date]<='Sales Revenue and Cost'[Date] &&
        'Exchange Rates'[To Currency]=SELECTEDVALUE('Reporting Currency'[Currency])
        )   
    )
)​

Acabo de probar su medida contra el gran conjunto de datos, y el rendimiento es terriblemente lento y volví a golpear errores de memoria del centro de datos de MS.

Sin embargo, siento que está en "algo", ¿hay alguna manera de que podamos evitar el cálculo línea por línea para cada transacción y agregar a un nivel más alto, por ejemplo, agrupado por Calendario[Año-Mes] y Pedidos[Moneda]; pensando en la próxima evolución de su última medida a algo así como;

Sales Revenue = 
VAR tab =
    SUMMARIZE (
        'Orders',
        'Calendar'[Year-Month],
        'Orders'[Currency],
        'Orders'[Price],
        "REp",
            CALCULATE (
                MIN ( Rates[Rate] ),
                FILTER (
                    Rates,
                    Rates[From Currency] IN DISTINCT ( Rates[From Currency] )
                        && 'Rates'[To Currency] IN DISTINCT ( ReportingCur[Currency] )
                        && 'Rates'[Date] <= MAX(Orders[Date])
                        && MAX('Rates'[Date])
                )
            )
    )
RETURN
    SUMX ( tab, [REp] * [Price] )

Aún así, ¿no se puede alcanzar el tipo de cambio correcto y la suma del precio convertido todavía no es correcta, pero al menos parece funcionar más rápido ya que el sumx se realiza en un nivel agregado más alto?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.