Convertir lista ponderada en vector o lista

[Anonymous]

Tengo un conjunto de datos de miles de productos con millones de transacciones, donde la mayoría de estas transacciones implican QTY <> 1. Una medida que estoy intentando calcular es comparar la asimetría del precio de un producto a lo largo del tiempo, pero esto implica transformar un conjunto de datos como:

Producto	Precio	Qty
A	$0.90	2
A	$1.00	1
A	$1.10	1
B	$1.00	1

Lista para A:

$0.90

$0.90

$1.00

$1.10

De esta manera, puedo derivar:

• Media: $0,975
• Mediana: $0,95
• Modo : $0.90
• Std Dev 0.082916
• 1a asimetría de Pearson: 0,90453
• 2a asimetría de Pearson: 0,90453

¿Alguna recomendación sobre cómo DAX puede crear una lista que repita valores basados en una segunda columna? Estoy indeciso para incluso construir una segunda tabla de hechos que hace esta transformación porque se volverá rediculously long. La creación de una tabla resumida con SQL o M no funcionará porque la lista debe poder asumir filtros en el resto del informe (por ejemplo, producto, cliente, fecha de transacción, &c.) Calcular media y modo son lo suficientemente fáciles de realizar con el conjunto de datos, pero necesito transformar los datos para calcular una mediana adecuada y una desviación estándar.

[v-kelly-msft]

Hola @robjensen ,

En primer lugar, cree una columna calculada como se indica a continuación:

Column = REPT(CONCATENATE("$"&'Table'[Price]," "),'Table'[Qty])

A continuación, cree una medida como se indica a continuación:

List for A = CONCATENATEX(FILTER(ALL('Table'),'Table'[Product]="A"),'Table'[Column]," ")

Y verás:

Para más detalles, pls adjunto.

Saludos
Kelly

¿He respondido a tu pregunta? ¡Marca mi puesto como una solución!

[mahoneypat]

Simplemente agregaría un par de pasos a su consulta existente para hacer las filas adicionales, por lo que su análisis posterior será fácil. Este es un ejemplo sobre cómo puede usar List.Repeat y el valor Qty para obtener la tabla modificada de los datos de ejemplo. Para ver cómo funciona, simplemente cree una consulta en blanco, vaya a Editor avanzado y reemplace el texto allí con el código M a continuación.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVIx0LM0ANJGSrE6UBFDPQOQiCGyiCFCxAlVTSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Price = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Price", Currency.Type}, {"Qty", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Repeat({[Price]}, [Qty])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Currency.Type}})
in
    #"Changed Type1"

Si esto funciona para usted, márquelo como la solución. Los elogios también son apreciados. Por favor, avísame si no.

saludos

palmadita

[Anonymous]

Hola @mahoneypat ,

Aunque esta solución funciona en principio, está fallando en la aplicación para mi caso de uso. El problema con el que me estoy metiendo es que los procesos increíblemente ineficientes en cuanto a recursos que se están ejecutando en segundo plano en PowerQuery. Aquí está mi guión M:

let
    Source = Oracle.Database("DSPROD", [HierarchicalNavigation=true, Query="
select DIM_CUST_CURR_ID,
       dim_item_curr_id,
       UNIT_PRICE,
       sum(sell_qty) as PRMRY_SELL_QTY
  from FOO
where dim_ordr_dt_id >= TO_NUMBER(TO_CHAR(SYSDATE-31,'YYYYMMDD'))
having round(sum(sell_qty),0) > 0
GROUP BY DIM_CUST_CURR_ID,
         dim_item_curr_id,
         UNIT_PRICE
--FETCH FIRST 10 ROWS ONLY"]),
    #"Added Custom" = Table.AddColumn(Source, "Price", each List.Repeat({[UNIT_PRICE]}, Number.Round([PRMRY_SELL_QTY], 0))),
    #"Expanded Prices_1" = Table.ExpandListColumn(#"Added Custom", "Price"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Prices_1",{"DIM_CUST_CURR_ID", "DIM_ITEM_CURR_ID", "UNIT_PRICE"})
in
    #"Removed Other Columns"

Cuando descargo un historial de transacciones de 40 días, empiezo con una trama de datos de 4 columnas de ancho (número de cuenta del cliente, número de artículo, precio, qty) y registros de 1 MM de largo, y el csv correspondiente (la misma consulta es expulsada por otro programa SQL con fines debuggin) es de 25 MB. Después de transformar en consecuencia, tengo una trama de datos que tiene 3 columnas de ancho, 900MM+ filas de largo, y PowerQuery de alguna manera consume 42 GB de caché en mi disco duro.

Necesito comparar este historial de 40 días con el historial de ventas de 30 días, que está en tendencia a unas 4 columnas iniciales x 600K filas, se transforma en 3 columnas x 650MM filas, y también consume un adicional de 30 GB de caché en mi disco duro.

La única manera de que mi modelo se actualice es actualizar manualmente cada tabla de forma independiente porque mi equipo se queda sin espacio en disco. Además, sospecho que este procesamiento no volará cuando se refresca con el servicio en línea. Afortunadamente, el resultado final de los datos aumenta el tamaño del archivo .pbix en menos de 50 MB, por lo que sé que el problema no es la cantidad de datos, sino que PowerQuery no es la herramienta adecuada para el trabajo. Lamentablemente, solo puedo usar Python o R para realizar las manipulaciones si uso una puerta de enlace personal en lugar de la puerta de enlace empresarial 😞

[mahoneypat]

Son muchas filas. Pensé más en esto y se me ocurrió una manera en DAX de hacer su tabla virtualmente (también podría adaptarse para que sea físicamente en una tabla calculada DAX).

Sé que probablemente te quedarás con R o Python, pero este fue un desafío divertido, así que trabajé en ello más. Consulte las medidas a continuación. La media era directa, el modo era un poco más complicado, pero Median y StdDev son los que utilizan la tabla virtual. El de Pearson se calcula a partir de la media, la mediana y el stddev (esperemos que ese enfoque funcione para usted).

Mean =
SUMX ( Data, Data[Price] * Data[Qty] )
    / SUM ( Data[Qty] )

Mode =
TOPN ( 1, VALUES ( Data[Price] ), CALCULATE ( SUM ( Data[Qty] ) ) )

Median = 
VAR numberofvalues =
    SUM ( Data[Qty] )
VAR sequencetable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Price], Data[Qty] ),
        "minsequence",
        VAR thisprice = Data[Price]
        RETURN
            CALCULATE (
                SUM ( Data[Qty] ),
                ALL ( Data[Price], Data[Qty] ),
                Data[Price] < thisprice
            ) + 1,
        "maxsequence",
        VAR thisprice = Data[Price]
        VAR thisqty = Data[Qty]
        RETURN
            CALCULATE (
                SUM ( Data[Qty] ),
                ALL ( Data[Price], Data[Qty] ),
                Data[Price] < thisprice
            ) + thisqty
    )
VAR numberseries =
    ADDCOLUMNS (
        GENERATESERIES ( 1, numberofvalues, 1 ),
        "PriceValue",
        VAR thisnumber = [Value]
        RETURN
            MINX (
                FILTER (
                    sequencetable,
                    AND ( [minsequence] <= thisnumber, [maxsequence] >= thisnumber )
                ),
                Data[Price]
            )
    )
RETURN
    MEDIANX(numberseries, [PriceValue])


Std Dev = 
VAR numberofvalues =
    SUM ( Data[Qty] )
VAR sequencetable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Price], Data[Qty] ),
        "minsequence",
        VAR thisprice = Data[Price]
        RETURN
            CALCULATE (
                SUM ( Data[Qty] ),
                ALL ( Data[Price], Data[Qty] ),
                Data[Price] < thisprice
            ) + 1,
        "maxsequence",
        VAR thisprice = Data[Price]
        VAR thisqty = Data[Qty]
        RETURN
            CALCULATE (
                SUM ( Data[Qty] ),
                ALL ( Data[Price], Data[Qty] ),
                Data[Price] < thisprice
            ) + thisqty
    )
VAR numberseries =
    ADDCOLUMNS (
        GENERATESERIES ( 1, numberofvalues, 1 ),
        "PriceValue",
        VAR thisnumber = [Value]
        RETURN
            MINX (
                FILTER (
                    sequencetable,
                    AND ( [minsequence] <= thisnumber, [maxsequence] >= thisnumber )
                ),
                Data[Price]
            )
    )
RETURN
    STDEVX.P(numberseries, [PriceValue])


Pearson's Skewness = 3*([Mean] - [Median])/[Std Dev]

Nota: El modo puede dar un error si usted tiene dos precios que ocurren el mismo número de veces. Me centré en hacer la tabla virtual para Median y StdDev, y puedo editar este post más tarde para dar un modo más robusto (se quedó sin tiempo ahora). Con suerte, puede usar el enfoque para crear la tabla virtual con los valores repetidos para satisfacer sus necesidades.

Si esto funciona para usted, márquelo como la solución. Los elogios también son apreciados. Por favor, avísame si no.

saludos

palmadita