Grouping values to total 100%

[Anonymous]

Hello PBI community. I have a project where I am trying to 'automate' a process. This involves me grouping numbers together that best/closest add up to 100%. I have one column that is 'Names', and another column that's 'Percent'. The groups can have 2-5 names, 2 names being the highest priority. I'd like the number of names in a group to be dynamic, but not a requirement. So, looking at the sample data below, pairing Name 989 and 1266 would give us 100.39%, then pairing the next 2 that would give us closest to 100%, if the group of 2 falls outside of 92%, group 3 together, if the groups of 3 fall outside of 92%, group 4 together and so on. I'd like for the results to be in a table format as below (not actual results, just example of table format). Please let me know if there are any questions, I greatly appreciate the help! 

I was able to do this in Excel. I tried groups in PBI but was not able to sum, and the DAX began to break as my code grew :D. 

Names	Percent
989	64.20237809
1353	62.37515013
911	59.79395868
532	58.7573865
531	58.66292337
1342	49.36307951
331	48.59470334
5022	48.41214269
2706	48.23443334
307	47.9356834
777	47.47543057
408	47.25912803
1016	46.04299651
405	45.88115542
505	45.53459044
1163	45.38728081
7301	45.36938506
909	45.0906798
1206	44.64911122
2822	44.04510418
75	44.03519097
2938	43.97736008
457	43.20766274
23	42.5981864
935	42.36788374
533	42.25756666
5056	40.77774442
912	40.37328849
839	40.29594043
450	39.426135
961	39.3512491
309	39.29275763
3288	39.25165603
386	39.20502042
1204	37.68868604
428	37.62603878
3868	37.16898871
415	37.12989431
543	37.10032518
542	36.8671631
170	36.65121307
1266	36.19325006

Example Table Format:

Groups	Total Percent
989, 1353	100.56
911, 532	102.35
531, 1342, 331	102.56

[lbendlin]

There is a better pairing (1353,428) with 100.0011889 %.  So I would propose an approach that finds the best pairing, removes it from the list and then repeats the process until the threshold is hit (8% delta, from 92% to 108%). Then try the same with triples etc.

[Anonymous]

How would you approach the best pairing then removing from the list?

[lbendlin]

Here is the first iteration:

Table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NZNLjgQxCEPvUutWBJjvWVpz/2sMKehaPhHHMa7v96ms5/O4HiFBJNXz9/k+DMPFchDGRowXF3NTqxOFsvR8qUEuzRMWSLeFPNBdSoBYXb2zWgcOijJ+Md5hzWOlQYCOBIkMVhZW8fEmQT5YoIrfdMtdGqdgngsjFmqYgmxcKOVQsWJJmscx8avrh1SqfL0p2aV2MpnN2v94W2pQK9K5jtkxGBktnCMRIF7shbT2/6ZJNZSKPGrCZJnX6XHtuFnmPsnJQtucMSnPdNhCGLfcPE/6jotxKgJONLNqMVQoeikxluU1LJ18cvqwTnAgPDKxk4YdFQvz/n5BvH7pdNahuvl0rkMRkEyd1SVqqFRvmhRrjJqiTq+4izfnnYf1w6SD2BXXwG5Ud83n+NVfbOzmu85u4lLqItH66nj14jie3V+neZtKLpU+n5E/icXsWZmxjWBbKv33KIZ2v5YSoZ3sv/H2HX7So8sxoxw0sDsmfIs71twHc7UA3Zb8/QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Percent = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "Paired", each null,type text),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Sum", each null,type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Percent", type number}}),
    Pass = List.Accumulate({0..20},#"Changed Type",(state,current)=> TwoStep(state))
in
    Pass 

(the "20"  should ideally be replaced by half the size of the list)

TwoStep:

(tbl)=>
let 
    t=Table.Buffer(tbl),
    #"Invoked Custom Function" = Table.AddColumn(t, "Twos", each Twos(Table.SelectRows(t,each [Paired]=null))),
    #"Replaced Value" = Table.ReplaceValue(#"Invoked Custom Function",each [Paired],each 
//if [Twos]{0}[Names]=[Twos]{0}[Pair] then [Paired] else 
if [Names]=[Twos]{0}[Names] then [Twos]{0}[Pair] else
if [Names]=[Twos]{0}[Pair] then [Twos]{0}[Names] else
 [Paired],Replacer.ReplaceValue,{"Paired"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Sum],each if [Names]=[Twos]{0}[Names] or [Names]=[Twos]{0}[Pair] then [Twos]{0}[Percent]+[Twos]{0}[Percent.1] else [Sum],Replacer.ReplaceValue,{"Sum"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value1",{"Names", "Percent", "Paired", "Sum"})
in
    #"Removed Other Columns"

Twos:

(tbl)=>
let
    #"Added Custom1" = Table.AddColumn(tbl, "Match", (k)=> Table.SelectRows(tbl,each [Names]<>k[Names])),
    #"Expanded Match" = Table.ExpandTableColumn(#"Added Custom1", "Match", {"Names", "Percent"}, {"Pair", "Percent.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Match", "Diff", each Number.Abs(100-[Percent]-[Percent.1]),type number),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Diff", Order.Ascending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1)
in
    #"Kept First Rows"

As you can see from the result some of the mappings are below the threshold. Those would then be candidates for the next step - testing the triples.

I also have to check why the "above 100"  matches are so underrepresented.

[lbendlin]

Looking at the rest of the data - there is no chance for any of the remaining values to group in triplets without exceeding the 8% threshold.

So you would have to play with different matching strategies. For example match the high end (108%) first, or change the threshold, or whatever other criteria is meaningful for your situation.