Create Average Column using criteria from another column

[Covington]

I have some sports data (NBA) that I am looking to create positional averages from.  Below is an example of the dataset:

Team	Position	PTS.Avg
DET	C	18.5
DET	PG	25.2
ATL	C	21
ATL	PG	23.7

I need to be able to Create averages for each Position (Across All Teams) to show the average PTS per position.  The final table would look like this (except with all teams/positions:

Team	Position	PTS.Avg	POS.Avg
ATL	C	21	19.75
DET	C	18.5	19.75
ATL	PG	23.7	24.45
DET	PG	25.2	24.45

Essentially running an Average based upon the Position and then returning that average to the POS.Avg column.

The purpose is to then take that data and determine a Defense Vs. Position number (like: POS.AVG / PTS.AVG).  I've been working on this for a while and just cannot figure out how to get it done 😞

I very much appreciate any help!  Thank you.

[Covington · Accepted solution]

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.

[mahoneypat · Accepted solution]

Just add a DAX calculated column with this formula

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

Regards,

Pat

[Icey · Accepted solution]

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.

[mahoneypat · Accepted solution]

Just add a DAX calculated column with this formula

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

Regards,

Pat

[Covington · Accepted solution]

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.

[ToddChitt]

You posted this question in the Power Query forum, but I think the solution is best done in DAX. 

You should investigate the ALL and ALLSELECTED DAX functions to tag along in your AVG function.