Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Weight based on multiple GROUPBY conditions

Hi Everyone,   This is my first community post, I apolgise in advance if the structure of the question is unclear (I have read throught the guidelines of asking questions so will try to stick them ...
General Comment
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    Pste this into a new blank query using Advanced Editor:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI21Dc00jcyMDIEcsLCwoCkoZGpUqwOFumIiAggaY4ka6BvYImm2cAAuzREM0zaCd3syMhIIGmGQzYqKgpIGiHJIhsN0WuBQxahNxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fund = _t, quarter = _t, security = _t, dollar_position = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"fund", type text}, {"quarter", type date}, {"security", type text}, {"dollar_position", Int64.Type}}),
    groupRows = Table.Group(chgTypes, {"fund", "quarter"}, {{"data", each _, type table [fund=nullable text, quarter=nullable date, security=nullable text, dollar_position=nullable number]}, {"dollar_posn_sum", each List.Sum([dollar_position]), type nullable number}}),
    expandData = Table.ExpandTableColumn(groupRows, "data", {"security", "dollar_position"}),
    addWeight = Table.AddColumn(expandData, "weight", each [dollar_position] / [dollar_posn_sum])
in
    addWeight

     

     

    This gives me the following output:

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi Anonymous ,

 

Pste this into a new blank query using Advanced Editor:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI21Dc00jcyMDIEcsLCwoCkoZGpUqwOFumIiAggaY4ka6BvYImm2cAAuzREM0zaCd3syMhIIGmGQzYqKgpIGiHJIhsN0WuBQxahNxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fund = _t, quarter = _t, security = _t, dollar_position = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"fund", type text}, {"quarter", type date}, {"security", type text}, {"dollar_position", Int64.Type}}),
    groupRows = Table.Group(chgTypes, {"fund", "quarter"}, {{"data", each _, type table [fund=nullable text, quarter=nullable date, security=nullable text, dollar_position=nullable number]}, {"dollar_posn_sum", each List.Sum([dollar_position]), type nullable number}}),
    expandData = Table.ExpandTableColumn(groupRows, "data", {"security", "dollar_position"}),
    addWeight = Table.AddColumn(expandData, "weight", each [dollar_position] / [dollar_posn_sum])
in
    addWeight

 

 

This gives me the following output:

 

Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you very much BA_Pete ! This worked like a charm !

     

    I'm just going to post my adapted Advanced Query for anyone struggling with the Source (It took me longer than expected although I'm a complete newbie to PowerBI)

    Perhaps its worth including in your answer ?

     

    Kindest,

     

    JC

     

    let
  Source = Excel.Workbook(
    File.Contents("C:\Users\..\Downloads\power_bi_question_sample_data.xlsx"), 
    null, 
    true
  ){[Item = "Sheet2", Kind = "Sheet"]}[Data], 
  #"Promote Header" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), 
  #"Transform Column" = Table.TransformColumnTypes(
    #"Promote Header", 
    {
      {"fund", type text}, 
      {"quarter_end_date", type date}, 
      {"security", type text}, 
      {"dollar_position", Int64.Type}
    }
  ), 
  #"Group Rows" = Table.Group(
    #"Transform Column", 
    {"fund", "quarter_end_date"}, 
    {
      {
        "data", 
        each _, 
        type table [
          fund = nullable text, 
          quarter = nullable date, 
          security = nullable text, 
          dollar_position = nullable number
        ]
      }, 
      {"dollar_posn_sum", each List.Sum([dollar_position]), type nullable number}
    }
  ), 
  #"Expand Data" = Table.ExpandTableColumn(#"Group Rows", "data", {"security", "dollar_position"}), 
  #"Add Column" = Table.AddColumn(
    #"Expand Data", 
    "weight", 
    each [dollar_position] / [dollar_posn_sum]
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Add Column", {"dollar_posn_sum"})
in
  #"Removed Columns"

    @