Average of Minimum and Maximum value based Group

[Junaid11]

Hello,

I have this table below and I want two measures 

1)1) One that takes average of the sum of all values by group (category and date), but only for the lowest order value per group and counts an average upon that. Example values are circled in the screenshot. 17 / 4 = 4.25

2) A measure that takes average of the sum of all values by group (category and date), but only for the HIGHEST order value per group , substracts it by the LOWEST and counts an average upon that.

Measure 1 is purple: 4+6+2+5 = 17.
17/4= 4.25

Measure 2 is red: 5+7+6+7=25
25/4=6.25 - 4.25 = 2

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBLCsAgDATQu7hW0JjoYcR1ly2F3r9aVPylUKHCY0wmBGGEFMd9Plf+AVTaKdAA6YLp5C/KIIBVTWZmWWYL/RiyjAod02BmrhsNWYVVZka/YW0DxzLfp42t0dTHvjXi6rAsG+oYF6VptJUtaa4kbsqdZ/PLCttHW5phGdU+4gs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order = _t, category = _t, date = _t, value = _t, DAX_column_previous_value = _t, #".." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"order", Int64.Type}, {"category", type text}, {"date", type date}, {"value", Int64.Type}, {"DAX_column_previous_value", Int64.Type}, {"..", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index", "DAX_column_previous_value", ".."})
in
#"Removed Columns"

The one with the lowest order number in a group (purple)
The one with the highest order number in a group (red).

Your help would be greatly appreicated.

Thank you

[Junaid11]

Hello @amitchandak ,

I want the outcome like this below:

[amitchandak]

@Junaid11 , if you want to do it in power query, duplicate table, using group by (category)  and take Avg of value.

https://docs.microsoft.com/en-us/power-query/group-by

Merge this new table back with orginal table

merge : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

in dax a new column

= averageX(Filter(Table, [Category] =earlier([Category]) ) , [Value])