Find The Highest aggregated option value

[Anonymous]

I have a table like this

Lead	Option 1	Value
1	Option 1	200
1	Option 2	100
1	Option 2	100
2	Option 1	200
2	Option 1	100
3	Option 1	200
3	Option 1	100

As it can be seen, each Lead can have multiple option and each option can have multiple line. 

So I need to find the highest value of each sumed option, ideally to a table like this

Lead	Best (highest value) Option	Corresponding Value
1	Option 2	Sum
2	Option 1	Sum
3	Option 1	Sum

How should I do it in Power BI ? Do I need 2 measue one for the Best Option Column and another for Corresponding value column ?

If possible can I have the solution for Power Query as well ?

[v-yalanwu-msft]

Hi, @Anonymous ;

Method 1 In power query.

1.group by 

2.add custom column.

let _Lead = [Lead], _option = [maxoption]
in List.Sum(Table.SelectRows(#"Changed Type", each [Lead] = _Lead and [Option 1] = _option)[Value])

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvKMnMz1MAMY0MDJRidVCEjYBMQ/zCRtgNQROGqTbGrtoYi+pYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lead = _t, #"Option 1" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lead", Int64.Type}, {"Option 1", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Lead"}, {{"maxoption", each List.Max([Option 1]), type nullable text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let _Lead = [Lead], _option = [maxoption]
in List.Sum(Table.SelectRows(#"Changed Type", each [Lead] = _Lead and [Option 1] = _option)[Value]))
in
    #"Added Custom"

Method 2 in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvKMnMz1MAMY0MDJRidVCEjYBMQ/zCRtgNQROGqTbGrtoYi+pYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lead = _t, #"Option 1" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lead", Int64.Type}, {"Option 1", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Lead"}, {{"maxoption", each List.Max([Option 1]), type nullable text}, {"all", each _, type table [Lead=nullable number, Option 1=nullable text, Value=nullable number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Option 1", "Value"}, {"Option 1", "Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded all", "Custom", each if [maxoption] = [Option 1] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Lead", "Option 1"}, {{"sumv", each List.Sum([Value]), type nullable number}})
in
    #"Grouped Rows1"

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks!

I actually managed to did it by grouping.

1. Group to find sum of each option 

2. Group again to find the highest option value of each lead

The key is to insert  A new column name that has operation as "All Row" to expand out again though. 

However now I'm having a new problem: 

My new table after grouping and expanding out

Lead	Option	Option Value	Max Option Value
1	1	100	200
1	2	200	200
2	1	300	300
2	2	300	300

As the above example, it's easy enough for lead 1 to add a custom column and compare Option value vs Max Option value to highlight the best one, but what should I do in the case of Lead 2, option 1 equal option 2 ? I would want to do something like if all option of a lead equal in value then pick option 1. 

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ,

Add another step -group by.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWADA6VYHQjfCIShfCOovDES3wjGjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lead = _t, #"Option 1" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lead", Int64.Type}, {"Option 1", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Lead"}, {{"maxvalue", each List.Max([Value]), type nullable number}, {"all", each _, type table [Lead=nullable number, Option 1=nullable text, Value=nullable number]}}),
    #"Expanded all1" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Option 1", "Value"}, {"all.Option 1", "all.Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded all1", "Custom", each if [maxvalue] = [all.Value] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Lead"}, {{"Minoption", each List.Min([all.Option 1]), type nullable text}, {"max", each List.Max([maxvalue]), type nullable number}})
in
    #"Grouped Rows1"

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.