Calculate Average, insert into columns conditionally

Hi Covington ,

Please check the following steps as below.

1. Unpivoted the fact table and make the group by the column Attribute. Here is the M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZDJDcUwCER78TlC2ICXLv49Sv9tfFYlF48EPM/AfbdfR2xXE9FnEMNS5RmC0E0YrCkDyGRp8bkctK7sBNlGF0yTDhiSxaEyCXaBwz9ykMJqwQ5CwjFBNxY4BdIXlJixFHQ8XDpqVJuZ+Dp6eSZIHw8uY4+x/TcFuUB5wThApqrlyJfj47xG1eM8fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, #"P-Code" = _t, a = _t, b = _t, c = _t, d = _t, e = _t, f = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"P-Code", Int64.Type}, {"a", type number}, {"b", type number}, {"c", type number}, {"d", type number}, {"e", type number}, {"f", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product", "P-Code"}, "Attribute", "Value"),
    #"GroupBy"=Table.Group(#"Unpivoted Columns", {"Attribute"},{{"AveragePrice", each List.Average([Value]), type number}})
in
    #"GroupBy"

2. Duplicated the fact table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZDJDcUwCER78TlC2ICXLv49Sv9tfFYlF48EPM/AfbdfR2xXE9FnEMNS5RmC0E0YrCkDyGRp8bkctK7sBNlGF0yTDhiSxaEyCXaBwz9ykMJqwQ5CwjFBNxY4BdIXlJixFHQ8XDpqVJuZ+Dp6eSZIHw8uY4+x/TcFuUB5wThApqrlyJfj47xG1eM8fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, #"P-Code" = _t, a = _t, b = _t, c = _t, d = _t, e = _t, f = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"P-Code", Int64.Type}, {"a", type number}, {"b", type number}, {"c", type number}, {"d", type number}, {"e", type number}, {"f", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product", "P-Code"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

3. Merge the two tables by the column Attribute.

let
    Source = Table.NestedJoin(Table1,{"Attribute"},#"Table1 (2)",{"Attribute"},"Table1 (2)",JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Product", "P-Code", "Value"}, {"Table1 (2).Product", "Table1 (2).P-Code", "Table1 (2).Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table1 (2)",{"Table1 (2).Product", "Table1 (2).P-Code", "Attribute", "Table1 (2).Value", "AveragePrice"})
in
    #"Reordered Columns"

By the way, we can create a calculated column in data pane.

Column = CALCULATE(AVERAGE('Table1 (2)'[Value]),ALLEXCEPT('Table1 (2)','Table1 (2)'[Attribute]))

Please find the pbix as attached.

Regards,

Frank