C# Data Table add a allocation (percentage column) for a employees having multiple rows

Mohammad Nadeem Alam 66 Reputation points
2023-02-02T16:58:20.5033333+00:00

Hi Everyone,
I need a logic to implement for the below using C#. I am able to do. but it is becoming bit lengthy. I need some efficient way to do the same.

Please find the below details. I have table as below.User's image

I need to add additional column percentage allocation as below.

User's image

Formula for will be as below

Users may get repeated, but their total percentage including all rows for allocation column will be always 100

Formula will be as below. Let’s take an example of Brutus Maximus. He has two rows

First rows value will go as = (17.325/(17.325+40.425))*100=(17.325/57.75)*100=30

Second rows value will go as =(40.425/(17.325+40.425))*100=(40.425/57.75)*100=70

It can be number of rows, it can be more than 2 rows as well for different employee.

For employee having one rows will be always 100

Let’s take an example of Mark Anthony=(42.25/42.25)*100=100

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
11,283 questions
{count} votes

Accepted answer
  1. Jack J Jun 24,641 Reputation points Microsoft Vendor
    2023-02-03T08:03:32.7166667+00:00

    @Mohammad Nadeem Alam, Welcome to Microsoft Q&A, you could try to group your datatable by the EmployeeName and get the sum of every Employee's Totoal Hours Earned.

    Here is a code example you could refer to.

                DataTable table = new DataTable();
                table.Columns.Add("EmployeeID", typeof(int));
                table.Columns.Add("EmployeeName", typeof(string));
                table.Columns.Add("CostCenterCode", typeof(int));
                table.Columns.Add("TotalHoursWorked", typeof(decimal));
                table.Columns.Add("TotalHoursEarned", typeof(decimal));
                table.Rows.Add(901234,"Mark",141005,41.5,42.25);
                table.Rows.Add(901367, "Julius", 141005, 0.5, 0.75);
                table.Rows.Add(901675, "Brutus", 1410013,15.45, 17.325);
                table.Rows.Add(901675, "Brutus", 1410013, 36.05, 40.425);
                table.Rows.Add(901675, "Steve", 1410013, 14.7, 15.45);
                table.Rows.Add(901675, "Steve", 1410013, 34.3, 36.05);
    
                var result = from row in table.AsEnumerable()
                             group row by row.Field<string>("EmployeeName") into m
                             select new
                             {
                                 EmployeeName=m.Key,
                                 TotalHoursEarned = m.Select(s => s.Field<decimal>("TotalHoursEarned")).Sum()
                             };
                table.Columns.Add("Allocation", typeof(int));
                foreach (DataRow item in table.Rows)
                {
                    foreach (var key in result)
                    {
                        if (item["EmployeeName"].ToString()==key.EmployeeName)
                        {
                            item["Allocation"] = (Convert.ToDecimal(item["TotalHoursEarned"])/key.TotalHoursEarned) * 100;
                        }
                    }
                }
    
    

    Result:

    User's image

    Hope my code could help you.

    Best Regards,

    Jack


    If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.