weighted average in POWER QUERY

[BarSi1986]

Hi All,

is there any idea to calculate weighted average inside POWER QUERY that will display correctly in Desktop on every level of data drilling ? cause if i do this as simple as you can, like: 

then after adding it to my bi project, we can throw TOTALS to trash can....  also i can do weighted average easily in DAX:

BUT the reason i want to achieve this in power query is that in the power query i can add DYNAMIC HEADERS to my columns and i can't do it in DAX instead

[Vera_33 · Accepted solution]

Hi @BarSi1986 

I don't see your original weightedAvg in Power Query is a weighted avg...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsrMTlXSUTICYlNLS6VYHbiQKRAbG5oiCxmClBmaGoDFkhOLQDyQZlNLIyQhYyA2MbOwQBIyB2sGaYwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [item = _t, quantity = _t, price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"item", type text}, {"quantity", Int64.Type}, {"price", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"item"}, {{"totalQ", each List.Sum([quantity]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"item"}, #"Grouped Rows", {"item"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"totalQ"}, {"totalQ"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "weightedAvg", each [price]*[quantity]/[totalQ], type number)
in
    #"Added Custom"

But if you get this column, simply drag and drop, the sum in Total is what you want?