How to Group Data, calculate Group percentage and insert Group Report summary line

Hi, i need some help, i have first excel1 xls

and i want to manipulate with condition if found a difference in ‘ID’, will have to insert new row, and then sum the ‘COUNT’ and make a percentage from every row in ‘COUNT’ (count/sum of count)

and the final result will be like this 2 xls

Hi @lentvalent

i think the count for second id B003 is different for both datatable

Can u check it and confirm please?

Regards

Nived N

@lentvalent
lets assume following dataset (simplified IDs, 2 cols)
grafik

Flowdescription:
preparing report datatable:
grafik

Grouping data:
grafik

Groups is of datatype: List(Of List(of Datarow)) outer list=groups, inner list=groupmembers

Group processing:

group sum: grp.Sum(Function (x) CInt(“0” & x(“Count”).toString))
group member add datarow: {gmbr(0),gmbr(1), (CInt(“0” & gmbr(1).toString)/Sum*100).toString(“F1”)}
report summary line (hardcoded 100% due rounding deviation fixes): {nothing,Sum, “100%”}

Result:
grafik

find starter help here:
GroupBy_1Col_AddStatisticSepRow.xaml (11.1 KB)

also have a look here:

1 Like

pardon me, fill wrong sum for another value, but that second sum is for B003 ID

thank you very much, @ppr it works perfect.
may i ask, in variable Groups = dtData.AsEnumerable.GroupBy(Function (x) x(“ID”).toString.Trim).Select(Function (g) g.ToList).toList

what is function x and function g?

perfect, it is working. Once the final tests are passed pleas mark the solving post as solution. So others can benefit from it. thanks

x, g can be undestood as local variables referenced within the LINQ statement

Have a look here:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.