# RadGridview With Weighted Average Totals in the bottom

1. ##### Ariel
9 posts
Member since:
Aug 2010

Posted 22 Nov 2010 Link to this post

Hi There

I need to implement a weighted Average Calculation in the group of the Grid, can you tell me if there any way I can implement this, as the functions define are not helping. I have tried this but it will not work
// string s_test = "Sum(YieldPrice * ProjectedBal) / Sum(ProjectedBal)";
// Item3 = new GridViewSummaryItem("YieldPrice", "WAVG : {0}", s_test);

string s_test = "Sum(YieldPrice * ProjectedBal) / Sum(ProjectedBal)";
Item3 = new GridViewSummaryItem("YieldPrice", "WAVG : {0}", s_test);

I have two columns YieldPrice  and ProjectedBal I want to take the Sum(YieldPrice * ProjectedBal) per row which works fine, and divide by the Sum(ProjectedBal) column

Regards

Bugsy
// string s_test = "Sum(YieldPrice * ProjectedBal) / Sum(ProjectedBal)";
// Item3 = new GridViewSummaryItem("YieldPrice", "WAVG : {0}", s_test);

3000 posts
Member since:
May 2009

Posted 22 Nov 2010 Link to this post

Hello Bugsy,

As far as I'm aware, this feature isn't available.
Regards,
Richard
3. ##### Alexander
306 posts

Posted 25 Nov 2010 Link to this post

Hello Bhavik,

The code snippet you provided will process the defined calculations for the RadGridView rows in the current view. It will include all RadGridView rows in a plain grid and the rows of the current group in a RadGridView with applied grouping.

As I understand your scenario, you need the Sum(ProjectedBal) to be calculated for all RadGridView rows while Sum(YieldPrice * ProjectedBal) should apply only for the rows of the current group. To achieve it, you can create a summary item which calculates Sum(YieldPrice * ProjectedBal) and use the GroupSummaryEvaluate event to define the final value of the summary item. The following code snippet demonstrates this approach:
`string` `expression = ``"Sum(YieldPrice * ProjectedBal)"``;`
`GridViewSummaryItem summaryItem = ``new` `GridViewSummaryItem(``"YieldPrice"``, ``"WAVG : {0}"``, expression);`
`this``.radGridView1.SummaryRowsTop.Add(``new` `GridViewSummaryRowItem(``new` `GridViewSummaryItem[] { summaryItem }));`

`private` `void` `radGridView1_GroupSummaryEvaluate(``object` `sender, GroupSummaryEvaluationEventArgs e)`
`{`
`    ``if` `(e.SummaryItem.Name == ``"YieldPrice"``)`
`    ``{`
`        ``RadCollectionView<GridViewRowInfo> dataView = ``null``;`

`        ``GridViewGroupRowInfo groupRow = e.Parent ``as` `GridViewGroupRowInfo;`
`        ``if` `(groupRow != ``null``)`
`        ``{`
`            ``dataView = groupRow.ViewTemplate.DataView;`
`        ``}`
`        ``else`
`        ``{`
`            ``GridViewTemplate template = e.Parent ``as` `GridViewTemplate;`
`            ``if` `(template != ``null``)`
`            ``{`
`                ``dataView = template.DataView;`
`            ``}`
`        ``}`

`        ``if` `(dataView != ``null``)`
`        ``{`
`            ``decimal` `projectedBalSum = (``decimal``)dataView.Evaluate(``"Sum(ProjectedBal)"``, 0, dataView.Count);`
`            ``e.Value = (``decimal``)e.Value / projectedBalSum;`
`        ``}`
`    ``}`
`}`

The Parent property of the GroupSummaryEvaluate event arguments refers to the GridViewTemplate in a plain RadGridView, the GridViewGroupRowInfo if grouping is applied and the GridViewHierarchyRowInfo if the summary row is in a child template.

I hope it helps.

Best regards,
Alexander
the Telerik team
Get started with RadControls for WinForms with numerous videos and detailed documentation.
4. ##### Ariel
9 posts
Member since:
Aug 2010

Posted 25 Nov 2010 Link to this post

Hi Alexander

the solutions you proposed has worked.

regards

Bhavik
5. ##### Ariel
9 posts
Member since:
Aug 2010

Posted 01 Dec 2010 Link to this post

HI Alex

I realize that the solution work in Calculating the value's when no group is applied however when I calculate the Wavg: within groups in the grid the SummaryRow is incorrect

For example

My grid is grouped by a status, there is a summary Row in each of the Status group's however the data is calculating the Weighted aveage based on the Total sum of the entire dataset and not the sum belonging to the Group.

The entire Grid has 500 Records grouped by status

Group - Active
Column 1        Column2
231877.6   * 4.49   = 1041130/ 595516.1 =  1.748282
+363638.6  *    4.09  = 1487282/ 595516.1 =   2.497467
595516.1  4.245749 (Should be this value but is like 0.010)

Group - Retire
etc....

The Total of all 500 records for columns one in like 2 milion the calculation is taking the 1041130/ 2mil and 1487282 / 2mil and adding them.

regards

B
##### Alexander
306 posts

Posted 06 Dec 2010 Link to this post

Hello Bhavik,

The example from my previous answer is processing the following calculations:

1. In a flat RadGridView (no grouping applied):
- multiplies the values of the two columns for every row and sums them;
- divides the result to the sum of the values in the second column.

2. In RadGridView with applied grouping:
- multiplies the values of the two columns for every row in the group and sums them;
- divides the result to the sum of the values in the second column for all RadGridView rows.

If according to your requirements all calculations for a summary row in a group should be processed only for the rows of that group, your initial solution should work (without using the GroupSummaryEvaluate event):
`string` `expression = ``"Sum(YieldPrice * ProjectedBal)) / Sum(ProjectedBal)"``;`
`GridViewSummaryItem summaryItem = ``new` `GridViewSummaryItem(``"YieldPrice"``, ``"WAVG : {0}"``, expression);`
`this``.radGridView1.SummaryRowsTop.Add(``new` `GridViewSummaryRowItem(``new` `GridViewSummaryItem[] { summaryItem }));`

Please give me more details (a simple example would be best) of your requirements, so I can assist you further.

Best regards,
Alexander
the Telerik team
Get started with RadControls for WinForms with numerous videos and detailed documentation.
7. ##### Brennan
39 posts
Member since:
Sep 2010

Posted 06 Mar 2015 in reply to Alexander Link to this post

How can we test if "Sum(ProjectedBal)" is 0? If it is, string expression = "Sum(YieldPrice * ProjectedBal)) / Sum(ProjectedBal)"; fails miserably.

Thanks,
8. ##### Hristo
1015 posts

Posted 11 Mar 2015 Link to this post

Hi Brennan,

Thank you for writing.

You would need to manually ensure that the value is valid, otherwise you would receive an inner exception of type System.DividedByZeroException  You could perform a check for the calculated projectedBalSum if it was to equal 0. Please see my code snippet below:
`public ``partial ``class ``Form1 : Form`
`{`
`    ``public ``Form1()`
`    ``{`
`        ``InitializeComponent();`

`        ``DataTable dataTable = ``new ``DataTable();`
`        ``dataTable.Columns.Add(``"YieldPrice"``, ``typeof``(``decimal``));`
`        ``dataTable.Columns.Add(``"ProjectedBal"``, ``typeof``(``decimal``));`

`        ``for ``(``int ``i = 0; i < 100; i++)`
`        ``{`
`            ``//dataTable.Rows.Add(i, i + 10);`
`            ``dataTable.Rows.Add(i, 0);`
`        ``}`

`        ``this``.radGridView1.DataSource = dataTable;`
`        ``this``.radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;`

`        ``string ``expression = ``"Sum(YieldPrice * ProjectedBal)"``;`
`        ``GridViewSummaryItem summaryItem = ``new ``GridViewSummaryItem(``"YieldPrice"``, ``"WAVG : {0}"``, expression);`
`        ``GridViewSummaryRowItem summaryRowItem = ``new ``GridViewSummaryRowItem(``new ``GridViewSummaryItem[] { summaryItem });`

`        ``this``.radGridView1.SummaryRowsTop.Add(summaryRowItem);`
`    ``}`

`    ``private ``void ``radGridView1_GroupSummaryEvaluate(``object ``sender, GroupSummaryEvaluationEventArgs e)`
`    ``{`
`        ``if ``(e.SummaryItem.Name == ``"YieldPrice"``)`
`        ``{`
`            ``RadCollectionView<GridViewRowInfo> dataView = ``null``;`

`            ``GridViewGroupRowInfo groupRow = e.Parent ``as ``GridViewGroupRowInfo;`
`            ``if ``(groupRow != ``null``)`
`            ``{`
`                ``dataView = groupRow.ViewTemplate.DataView;`
`            ``}`
`            ``else`
`            ``{`
`                ``GridViewTemplate template = e.Parent ``as ``GridViewTemplate;`
`                ``if ``(template != ``null``)`
`                ``{`
`                    ``dataView = template.DataView;`
`                ``}`
`            ``}`

`            ``if ``(dataView != ``null``)`
`            ``{`
`                ``decimal ``projectedBalSum = (``decimal``)dataView.Evaluate(``"Sum(ProjectedBal)"``, 0, dataView.Count);`
`                ``if ``(projectedBalSum != 0)`
`                ``{`
`                    ``e.Value = (``decimal``)e.Value / projectedBalSum;`
`                ``}`
`                ``else`
`                ``{`
`                    ``RadMessageBox.Show(``"Sum of the values in column \"ProjectBal\" equls \"0\". Attempted to divide by zero."``);`
`                ``}`
`            ``}`
`        ``}`
`    ``}`
`}`

I hope this information helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo Merdjanov
Telerik

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.