Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I found this M code on the forum (From Marcel Beug) the other night.. I used it to sum then listing the top category sold by each sales rep. Wondering if there is a way to modify this code to list out the 'top 2' categories sold by each sales rep?
Solved! Go to Solution.
try this
Basically idea is to
1)Group by each Sales rep
2) Sort the Rows for each Sales Rep by Amout
3) Add an Index Column for each Sales rep
4) Select the Index values 1 and 2 i.e Top2 Sales Rep
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc69CoAwDEbRd+ns4H91FJycBN3EIYKgoLWIDr69lYTaoNMXznBJ14lqm5TwRA3zM4Hvi96zWgw7mMkYtqBHMwnD5tR6ucwRITewUvUwG7pI0dg1akrXbDJHbTd1vZ/GDPmjhDxKaKspcgnqE0WjpnSNJ9FsUSb/xf4G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesRep = _t, Category = _t, Amt = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesRep", type text}, {"Category", type text}, {"Amt", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"SalesRep"}, {{"All", each Table.AddIndexColumn(Table.Sort(_,{{"Amt", Order.Descending}}),"Index",1,1), type table}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Category", "Amt", "Index"}, {"Category", "Amt", "Index"}), #"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([Index] = 1 or [Index] = 2)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}) in #"Removed Columns"
try this
Basically idea is to
1)Group by each Sales rep
2) Sort the Rows for each Sales Rep by Amout
3) Add an Index Column for each Sales rep
4) Select the Index values 1 and 2 i.e Top2 Sales Rep
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc69CoAwDEbRd+ns4H91FJycBN3EIYKgoLWIDr69lYTaoNMXznBJ14lqm5TwRA3zM4Hvi96zWgw7mMkYtqBHMwnD5tR6ucwRITewUvUwG7pI0dg1akrXbDJHbTd1vZ/GDPmjhDxKaKspcgnqE0WjpnSNJ9FsUSb/xf4G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesRep = _t, Category = _t, Amt = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesRep", type text}, {"Category", type text}, {"Amt", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"SalesRep"}, {{"All", each Table.AddIndexColumn(Table.Sort(_,{{"Amt", Order.Descending}}),"Index",1,1), type table}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Category", "Amt", "Index"}, {"Category", "Amt", "Index"}), #"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([Index] = 1 or [Index] = 2)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}) in #"Removed Columns"
Thanks so much! This worked perfectly. 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |