[Solved] Grid Calculated Column use column total as data field.

[Sigifredo]

Hi,

I have a grid with a bound column "Sales" and need to add a calculated column "Percentage" which will be the result of  Sales/Sum(Sales)*100. How can I accomplish this?

I've tried different ways but I can't get it to work. Below is an example of what i am trying to do...  

<telerik:GridBoundColumn DataField="Sales" DataType="System.Decimal" HeaderText="Sales"
 SortExpression="Sales" UniqueName="Sales" Aggregate="Sum">                 
</telerik:GridBoundColumn>
<telerik:GridCalculatedColumn HeaderText="Sales %" UniqueName="Percentage"
DataFields ="Sales, sum(Sales)" Expression="{0}/{1}" DataType="System.Double" Aggregate="Sum" >
</telerik:GridCalculatedColumn>

Thanks in advance,

Regards,
Sigi Perez

[Shinu]

Hi,

aspx:

<telerik:GridCalculatedColumn UniqueName="Name" HeaderText="Calculated Column" DataFields="Sales, Sum" DataFormatString="{0:d}%" Expression="{0}*10">
</telerik:GridCalculatedColumn>

Thanks,
Shinu.

[Sigifredo]

Hi Shinu,

Thanks for the response but I don't think what you send me is what I am looking for. Maybe, I wasn't clear on the issue I am having... 

I have a grid with one bound column "sales" and has the aggregate=Sum (see below)... 

<telerik:GridBoundColumn DataField="Sales" DataType="System.Decimal" HeaderText="Sales"
  SortExpression="Sales" UniqueName="Sales" Aggregate="Sum">                 
</telerik:GridBoundColumn>

 I need a calculated column to show the percentage of the "sales". The calculation will be based on the "sales" divided by "the total sum of sales"...

Example: 

Sales	Sales %
34,995	5.2%
2,332	0.3%
387,515	57.7%
4,198	0.6%
243,122	36.2%
672,162	100.0%

When I use the sample you send me I get and the error: Cannot find column [Sum]. 


Thanks 
Sigi Perez

[Shinu]

Hi,

Please try following code snippet to show Percentage value in GridCalculatedColumn.

C#:

protected void RadGrid1_PreRender(object sender, EventArgs e)
{
    GridFooterItem footerItem = (GridFooterItem)RadGrid1.MasterTableView.GetItems(GridItemType.Footer)[0];
    string footer = footerItem["Number"].Text;
    string sum = Regex.Replace(footer, "[^0-9]+", string.Empty);
    double sumvalue = Convert.ToInt32(sum);
    foreach (GridDataItem DataItem in RadGrid1.Items)
    {
        string BoundColumnValue = DataItem["Sales"].Text;
        double value = Convert.ToInt32(BoundColumnValue);
        double Calculate = (value / sumvalue) * 100;
        DataItem["Percentage"].Text = (Calculate.ToString()+"%");

    }
}

Thanks,
Shinu.

[Sigifredo]

Thanks Shinu.

This should do the trick. I will implement it on my project but for what i see it will work.

Regards,

Sigi

[Manish]

I have a related problem

I am using a Grid Calculated Column for Calculating the Percentage

 

 

DataFields="B,A"

 

So my

Expression

 

 

="(({0}-{1})/{1})*100"
SO there are SCenarios where A is Zero , so how to handle the Divided by Zero Problem here.

also there are scenarios where B-A is a Negative Number  as a numerator , so how to handle the percent calculation for -Ve number.



I dont want to do all this on ItemDataBound Event, so i used this Grid Calculayed Column which is much cleaner.

Any help on this will be appreciated.

Thanks
Manish

 

[Shinu]

Hi,

Check the following forum thread which discussed similar scenario.
Divide By Zero On RadCalculatedColumn

Thanks,
Shinu

[Manish]

can you please provide any solution to export the GridCalculateColumn when doing export to excel , without doing the manual calculation in the app code.
 my expression is ="(({0}-{1})/{1})*100"

so suppose -( (10-5)/5) * 100)  , it should be 100 %  , but it shows 1 % , so somehow , its not multiplying by 100 when exported to excel

is there  a simple/ automatic way to export the gridCalculateColumn to Excel

any help is appreciated

Thanks
Manish

[Kostadin]

Hi Manish,

Please check out the following forum post.

Greetings,
Kostadin
the Telerik team

[Manish]

everything is same for me  , but export to excel is not showing the percent column properly , its not doing part of the calculation when exporting to excel. also found that EqualTo filter is also not working for GridCalculated column with 

 

DataFormatString

 

 

="{0:###,##0.00}%"   or

 

DataFormatString

 

 

="{0:c}%"

 

 

 

do you see any problem in this expression

 

Expression

 

 

="iif({1}=0,0,iif(({0}-{1})=0,0,(({0}-{1})/{1})*100))"

 

however this expression is giving me a perfect output on Grid display

see the entire column

<

 

 

telerik:GridBoundColumn UniqueName="A" DataField="A" HeaderText="A"

 

 

 

ItemStyle-HorizontalAlign="Right" HeaderStyle-HorizontalAlign="Right" DataType="System.Decimal" Visible="true"

 

 

 

AllowFiltering="true" FilterControlWidth="45px" DataFormatString="${0:###,##0.00}" />

 

 

 

<telerik:GridBoundColumn UniqueName="B" DataField="B" HeaderText="B"

 

 

 

ItemStyle-HorizontalAlign="Right" HeaderStyle-HorizontalAlign="Right" DataType="System.Decimal" Visible="true"

 

 

 

AllowFiltering="true" FilterControlWidth="45px" DataFormatString="${0:###,##0.00}" />

 

<

 

 

telerik:GridCalculatedColumn HeaderText="Percent" HeaderStyle-HorizontalAlign="Right" ItemStyle-HorizontalAlign="Right"

 

 

 

UniqueName="Percent" DataType="System.Double"

 

 

 

DataFields="A, B" Expression="iif({1}=0,0,iif(({0}-{1})=0,0,(({0}-{1})/{1})*100))" FooterText="" AllowFiltering="true" FilterControlWidth="30px" Aggregate="Avg"

 

 

 

DataFormatString="{0:###,##0.00}%" FooterStyle-HorizontalAlign="Right" FooterStyle-Font-Bold="true" />

 

tried DataType="System.Double" too, no luck
any idea?

[Manish]

Solved the equal to filter issue mentioned above  by using the convert in the expression, now using

Expression="iif({1}=0,0,iif(({0}-{1})=0,0, CONVERT( (({0}-{1})/{1})*10000, System.Int64 ) / 100  ))"

[Kostadin]

Hi Manish,

An answer could be found at following forum post.

Please note that opening multiple forum threads with the same issue makes tracking the tickets history very hard. In this regard, I suggest you to stick to the other forum post and close this one. This will enable us to respond more quickly and efficiently to your posts.

Greetings,
Kostadin
the Telerik team