Forum Discussion

BarSi1986's avatar
BarSi1986
Helper I
4 years ago
Solved

weighted average in POWER QUERY

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: ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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?

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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?