Group By and Aggregate Based on Aggregated Column

[Anonymous]

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:

Name	stepcount	duration
A	5	12.1
A	5	12.4
A	5	12.7
A	4	15
B	1	5.3
B	1	5.5
B	2	1.7

Desired Result

Name	StepcountMode	AvgBasedOnMode
A	5	12.4
B	1	5.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

[Mariusz · Accepted solution]

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]

Hi @Mariusz , thanks for the answer, i didn't think of that and it works! 😄