Create Average Column using criteria from another column

I ended up doing the following:

GroupedRows = Table.Group(#"Reordered Columns1", {"Position"}, {{"POS.AVG", each List.Average([PTS.Avg]), type nullable number}}),
#"MergedQueries" = Table.NestedJoin(#"Reordered Columns1",{"Position"},GroupedRows,{"Attribute"},"DvP",JoinKind.Inner)

Those 2 with a calc column worked great.

Just add a DAX calculated column with this formula

Avg Column = CALCULATE(AVERAGE(Table[Pts.Avg]), ALLEXCEPT(Table, Table[Position]))

Regards,

Pat

Hi Covington ,

If you want to create that column in Power Query Editor, you can also try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnENUdJRcgZiQws9U6VYHZhQgDuQMDLVMwKLOYb4QJUZGSIJQBQZ65krxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Position = _t, PTS.Avg = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Position", type text}, {"PTS.Avg", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Position"}, {{"Avg", each List.Average([PTS.Avg]), type nullable number}, {"All", each _, type table [Team=nullable text, Position=nullable text, PTS.Avg=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Team", "PTS.Avg"}, {"Team", "PTS.Avg"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All",{"Team", "Position", "PTS.Avg", "Avg"})
in
    #"Reordered Columns"

If you want to use DAX to create a calculated column or a measure, try this:

Calculated column:

POS.Avg Column 1 = 
AVERAGEX (
    FILTER (
        'Table (2)',
        'Table (2)'[Position] = EARLIER ( 'Table (2)'[Position] )
    ),
    [PTS.Avg]
)

POS.Avg Column 2 = 
CALCULATE (
    AVERAGE ( 'Table (2)'[PTS.Avg] ),
    FILTER (
        'Table (2)',
        'Table (2)'[Position] = EARLIER ( 'Table (2)'[Position] )
    )
)

Measure:

POS.Avg Measure 1 = 
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[Position] = MAX ( 'Table (2)'[Position] )
    ),
    [PTS.Avg]
)

POS.Avg Measure 2 = 
CALCULATE (
    AVERAGE ( 'Table (2)'[PTS.Avg] ),
    FILTER (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[Position] = MAX ( 'Table (2)'[Position] )
    )
)

POS.Avg Measure 3 = 
CALCULATE (
    AVERAGE ( 'Table (2)'[PTS.Avg] ),
    ALLEXCEPT ( 'Table (2)', 'Table (2)'[Position] )
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.