Calculate average of values of first month for each group

[Anonymous]

Hi All,

I have provided sample data below and explained my requirement. Please let me know if and how this can be done on PowerBI.

Data has first 4 columns and I want to get the values in column 5 on Power BI

i.e. Avg Purchase value of month - Avg Purchase value of first month of each product

Is this possible? 

Product ID	Order Month	Purchase Value	Avg Purchase Value for month	Incr in Avg Purchase Value compared to first month of Purchase
A	Apr-19	120	130	130-130=0
A	Apr-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	May-19	55	55	55-55=0
B	Sep-19	75	75	75-55=20
B	Dec-19	65	65	65-55=10

Thanks.

[Anonymous]

Do you need DAX or a calculation in Power Query? 'Cause to me it looks like you want Power Query to enrich the data. In Power Query this calculation is dead simple.

Best
D

[Anonymous]

Hi @Anonymous ,

Anything would be fine as long as it solves this. If Power Query is simple, could you please let me know how it can be done? Would be a big help. 

Thanks.

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

For your case, you could use EARILER Function to create a calculate column as below:

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))

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Here's the solution in 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"

Just paste this in a blank query in the Advanced Editor and you'll see it happening step by step.

Best

D

[TomMartens]

Hey @Anonymous ,

for my solution, I created three measures.
This calculates the average of the first month:

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)
    )
)

This calculated the average of the current month:

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)
    )
)

And the 3rd calculates the change:

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

This allows to create a table visual like this:

Please be aware that my solution is based on the usage of a Calendar table (it's always a good idea to use a dedicated calendar table).
I created a relationship between both tables, this makes it necessary that the Order Month column from your table is of the data type date.
The table visual is using the Month-Year column from the Calendar table. I used this very simple DAX statement to create a very simple Calendar table, there are much more sophisticated statements available, e.g. here: https://www.sqlbi.com/tools/dax-date-template/

As always if averages are used it's necessary to specify what has to happen, at the Total line

Hopefully this provides what you are looking for.

Regards,
Tom

[Anonymous]

Hi @TomMartens ,

Thank you for taking the time out to respond and explain in such detail. Will try this out and get back.