Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group By and Aggregate Based on Aggregated Column

Hi Community,

 

I have been stuck with these for a few hours, i would like to group by 'Name' and find the Mode for each Name . From the calcualted Mode , i would like find the average for those containing the mode value. Eg Group A, avg = (12.1+12.4+12.7)/3

 

Data:

Namestepcountduration
A512.1
A512.4
A512.7
A415
B15.3
B15.5
B21.7

 

Desired Result

NameStepcountModeAvgBasedOnMode
A512.4
B15.4

 

I tried doing like below but can't solve it

= Table.Group(#"Sorted Rows", {"Name"}, {{"Details", each _, type table [Name=text, duration=number, stepcount= number]},{"StepCountMode",each List.Mode([stepcount]), type number}, {"AvgBasedOnMode", each let mode = List.Mode([stepcount]) , x = List.Select([stepcount],each _ >0) in List.Average(List.Select([duration],each x= mode)), type number}})

 

 

Any help in Dax / Mquery is fine

Thanks in advance

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Try the below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIFYkMjPUOlWB0UARN0AXO4gAlIwBTMdQIxQWr0jNH4CHkjkBhIeywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, stepcount = _t, duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"stepcount", Int64.Type}, {"duration", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "stepcount"}, {{"Count", each Table.RowCount(_), type number}, {"AvgBasedOnMode", each List.Average([duration]), type number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Name"}, {{"tbl", each Table.First( Table.Sort( _,{{"Count", Order.Descending}}) ), type record }}),
    #"Expanded tbl" = Table.ExpandRecordColumn(#"Grouped Rows1", "tbl", {"stepcount", "AvgBasedOnMode"}, {"StepcountMode", "AvgBasedOnMode"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded tbl",{{"StepcountMode", Int64.Type}, {"AvgBasedOnMode", type number}})
in
    #"Changed Type1"

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

2 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Try the below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIFYkMjPUOlWB0UARN0AXO4gAlIwBTMdQIxQWr0jNH4CHkjkBhIeywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, stepcount = _t, duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"stepcount", Int64.Type}, {"duration", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "stepcount"}, {{"Count", each Table.RowCount(_), type number}, {"AvgBasedOnMode", each List.Average([duration]), type number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Name"}, {{"tbl", each Table.First( Table.Sort( _,{{"Count", Order.Descending}}) ), type record }}),
    #"Expanded tbl" = Table.ExpandRecordColumn(#"Grouped Rows1", "tbl", {"stepcount", "AvgBasedOnMode"}, {"StepcountMode", "AvgBasedOnMode"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded tbl",{{"StepcountMode", Int64.Type}, {"AvgBasedOnMode", type number}})
in
    #"Changed Type1"

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Mariusz , thanks for the answer, i didn't think of that and it works! ğŸ˜„