Calculate Average, insert into columns conditionally

[Covington]

I have a fairly simple table (I think), but I need to Calculate Averages, then insert them after the table is pivoted:

Product	P-Code	a	b	c	d	e	f
P100	55	234.7	46.7	40.1	44.5	52.3	57.1
P101	58	234.4	47.6	41.0	41.4	47.2	63.8
P102	57	233.7	47.8	41.5	44.4	47.5	55.9
P103	57	233.5	47.1	39.3	41.4	52.7	60.8
P104	56	233.3	44.4	44.8	41.7	48.1	60.4
P105	56	232.3	47.5	41.4	43.2	49.7	55.1

My data comes in like this. 

I need to do the following:

• Calculate the Averages of columns (a - f)
• Pivot the Table to look like this (using unpivot):

Product	P-Code	Attribute	Value	Average
P100	55	a	234.7	233.65
P100	55	b	46.7	46.85
P100	55	c	40.1	41.35
P100	55	d	44.5	42.76667
P100	55	e	52.3	49.58333
P100	55	f	57.1	58.85

That would just be the first product's pivot.  You can see the Average is the Average of each column overall (regardless of product).  I *Think* I need tothen do something like this (but don't know how):

• After Unpivot, Create New Column "Average"
• Somehow Insert the average based upon the Attribute value

It's confusing.  I've tried so many ways by now I can barely remember them all 😞

Thanks in advance.

[MFelix]

Hi @Covington,

I believe that the best option is to make the unpivot of the columns as you refer then create then create the following measure:

Average =
CALCULATE ( AVERAGE ( Table[Value] ); ALL ( Table[P-Code]; Table[Product] ) )

See result below for the P-Code 55:

Regards,

MFelix

[Covington]

I did give that a try... At first it told me a comma was needed.  Then it told me that the name CALCULATE wasn't recognized.  I copied directly and simply changed to my Tablename where you had  "Table".  I'll keep trying harder.. gotta be something I did wrong.

[Covington]

ok... I'm starting to think I'm in the wrong product?

I'm using Power Query Editor... I noticed I'm in PowerBI forum.  I bet there is no calculate where I'm at.

[v-frfei-msft · Accepted solution]

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

[v-frfei-msft]

Hi @Covington ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank