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: 

 

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

 

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?

     

1 Reply

  • 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?