Calcular el promedio de valores del primer mes para cada grupo

[Anonymous]

Hola a todos

He proporcionado datos de muestra a continuación y explicado mi requisito. Por favor, hágamelo saber si esto se puede hacer en PowerBI y cómo hacerlo.

Los datos tienen las primeras 4 columnas y quiero obtener los valores de la columna 5 en Power BI

es decir, Valor medio de compra del mes - Valor medio de compra del primer mes de cada producto

¿Es posible?

ID de producto	Mes del pedido	Valor de compra	Valor medio de compra por mes	Incr en el valor medio de compra en comparación con el primer mes de compra
A	Abr-19	120	130	130-130-0
A	Abr-19	140	130	130-130-0
A	Jun-19	155	145	145-130-15
A	Jun-19	135	145	145-130-15
A	Oct-19	150	150	150-130-20
B	Mayo-19	55	55	55-55-0
B	Sep-19	75	75	75-55-20
B	Dic-19	65	65	65-55-10

Gracias.

[Anonymous]

¿Necesita DAX o un cálculo en Power Query? Porque a mí me parece que quieres que Power Query enriquezca los datos. En Power Query este cálculo es muy simple.

Mejor
D

[Anonymous]

Hola @darlove ,

Cualquier cosa estaría bien siempre y cuando resuelva esto. Si Power Query es simple, ¿podría por favor hacerme saber cómo se puede hacer? Sería de gran ayuda.

Gracias.

[v-lili6-msft]

hola @prashanth_u

Para su caso, podría utilizar la función EARILER para crear una columna de cálculo como se muestra a continuación:

https://docs.microsoft.com/en-us/dax/earlier-function-dax

Result = 
VAR _firstordermonth=CALCULATE(MIN('Table'[Order Month]),FILTER('Table','Table'[Product ID]=EARLIER('Table'[Product ID]))) 
return

CALCULATE(AVERAGE('Table'[Purchase Value]),FILTER('Table','Table'[Product ID]=EARLIER('Table'[Product ID])&&'Table'[Order Month]=EARLIER('Table'[Order Month])))-
CALCULATE(AVERAGE('Table'[Purchase Value]),FILTER('Table','Table'[Product ID]=EARLIER('Table'[Product ID])&&'Table'[Order Month]=_firstordermonth))

Resultado:

y aquí está el archivo pbix de muestra, por favor pruébelo.

saludos

Lin

[Anonymous]

Esta es la solución de Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUcCwoUjAyMLQEcYwMQKSxgVKsDjZpE0xpr9I8uLSpKViRKQ5pY0xp/+QShG4DKAmSdgJL+yZWwqTBZpuaIkkGpxbAJM1NIQRC0iU1GSZpZgohYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductID = _t, OrderMonth = _t, #"Purchase Value" = _t, #"Avg Purchase Value for month" = _t]),
    #"Set Correct Types on Columns" = Table.TransformColumnTypes(Source,{{"ProductID", type text}, {"OrderMonth", type date}, {"Purchase Value", Int64.Type}, {"Avg Purchase Value for month", Int64.Type}}),
    #"Calculate First Month Averages by Product" =
        let
            Source = #"Set Correct Types on Columns",
            #"Removed Columns" = Table.RemoveColumns(Source,{"Avg Purchase Value for month"}),
            #"Grouped Rows" = Table.Group(#"Removed Columns", {"ProductID", "OrderMonth"}, {{"MonthlyAvg", each List.Average([Purchase Value]), type number}}),
            #"Added Custom" = Table.AddColumn(
                #"Grouped Rows", "ShouldRetain",
                (r) =>
                    let
                        MinOrderMonth = List.Min(
                            Table.SelectRows(
                                #"Grouped Rows",
                                each [ProductID] = r[ProductID]
                            )[OrderMonth]
                        ),
                        Output = r[OrderMonth] = MinOrderMonth
                    in
                        Output
                ),
            #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([ShouldRetain] = true)),
            #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"OrderMonth", "ShouldRetain"})
        in
            #"Removed Columns1",
    #"Join Data To First Month Averages" =
        Table.Join(
            #"Set Correct Types on Columns", "ProductID", #"Calculate First Month Averages by Product", "ProductID", JoinKind.Inner
        ),
    #"Add Increase" = Table.AddColumn(#"Join Data To First Month Averages", "Increase", each [Avg Purchase Value for month] - [MonthlyAvg]),
    #"Changed Increase to Decimal" = Table.TransformColumnTypes(#"Add Increase",{{"Increase", type number}})
in
    #"Changed Increase to Decimal"

Simplemente pegue esto en una consulta en blanco en el Editor avanzado y verá que sucede paso a paso.

Mejor

D

[TomMartens]

Hola @prashanth_u ,

para mi solución, creé tres medidas.
Esto calcula el promedio del primer mes:

first monthly average = 
AVERAGEX(
    VALUES('Table'[Product ID])
    , var _firstDate = 
        CALCULATE(
            FIRSTNONBLANK(
                'Calendar'[Date] 
                , CALCULATE(SUM('Table'[Purchase Value]))
            )
            , ALL('Calendar')
        )
    var currentmonthend = EOMONTH(_firstDate, 0)
    var currentmonthstart = EOMONTH(_firstDate , -1) + 1
    return  
    CALCULATE(
        SUM('Table'[Purchase Value])
        , DATESBETWEEN('Calendar'[Date] , currentmonthstart , currentmonthend)
    )
)

Esto calculó el promedio del mes actual:

monthly average = 
var currentmonthend = EOMONTH(MAXX(VALUES('Calendar'[Date]), 'Calendar'[Date]), 0)
var currentmonthstart = EOMONTH(MAXX(VALUES('Calendar'[Date]), 'Calendar'[Date]), -1) + 1
return
AVERAGEX(
    VALUES('Table'[Product ID])
    , CALCULATE(
        SUM('Table'[Purchase Value])
        , DATESBETWEEN('Calendar'[Date] , currentmonthstart , currentmonthend)
    )
)

Y el 3o calcula el cambio:

change = [monthly average] - [first monthly average] 

Esto permite crear una tabla visual como esta:

Tenga en cuenta que mi solución se basa en el uso de una tabla de calendario (siempre es una buena idea usar una tabla de calendario dedicada).
He creado una relación entre ambas tablas, esto hace necesario que la columna Mes de pedido de la tabla sea de la fecha de tipo de datos.
El objeto visual de tabla utiliza la columna Mes-año de la tabla Calendario. Usé esta instrucción DAX muy simple para crear una tabla Calendar muy simple, hay declaraciones mucho más sofisticadas disponibles, por ejemplo, aquí: https://www.sqlbi.com/tools/dax-date-template/

Como siempre si se utilizan promedios es necesario especificar lo que tiene que suceder, en la línea Total

Esperemos que esto proporcione lo que está buscando.

saludos
Tom

[Anonymous]

Hola @TomMartens ,

Gracias por tomarse el tiempo para responder y explicar con tanto detalle. Probaré esto y regresaré.