Forum Discussion
Calculate Average, insert into columns conditionally
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.
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
5 Replies
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.
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.
