Calculate Avg Based On Condition

Question

User-807418713 posted

Hello

This is my Gridview Data

Arrival Date	Name	Sale To	Pieces	Meter	Avg
1-Oct-18	Top Quality	John	10	50	5
2-Oct-18	Best M/S Quality		100	600	6
3-Oct-18	Low Quality		20	105	5.25
3-Oct-18	Best M/S Quality		1000	5107	5.11
4-Oct-18	Best M/S Quality		100	940	9.4
	Grand Total		1230	6802	5.54

Here I Want To Calculate Average Grand Total Take Some Of M/S Pieces & Meter Then Meter / Pieces here 6647/1200 = 5.54

how to do so using Gridview Row Databound..

Thanks

Answer 1

User753101303 posted

Hi,

My understanding is that you want to exclude row 1 and 3 from the grand total calculation but I'm not sure about your criteria. This is because you have less than x pieces ?

IMO the code is clearer when you are working on source data (you are using ADO.NET or EF ?) rather than when binding a control to data and then taking data from your control rather than from the original data source.

Answer 2

User839733648 posted

Hi Gopi.MCA,

According to your description, I suggest that you could Calculate Average Grand Total in the DataBound Event better.

I've made a sample on my side, and for more details, you could refer to the code below.

.Aspx

           <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="true" OnRowDataBound="GridView1_RowDataBound" OnDataBound="GridView1_DataBound">
                <RowStyle HorizontalAlign="Center" />
                <FooterStyle HorizontalAlign="Center" />
                <Columns>
                    <asp:BoundField DataField="Arrival Date" HeaderText="Arrival Date" ItemStyle-Width="150">
                        <ItemStyle Width="150px"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150">
                        <ItemStyle Width="150px"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="Sale To" HeaderText="Sale To" ItemStyle-Width="150">
                        <ItemStyle Width="150px"></ItemStyle>
                    </asp:BoundField>
                    <asp:TemplateField HeaderText="Pieces">
                        <ItemTemplate>
                            <asp:Label ID="lblPieces" runat="server" Text='<%# Eval("Pieces") %>'></asp:Label>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Label ID="PiecesTotal" runat="server"></asp:Label>
                        </FooterTemplate>
                        <ItemStyle Width="150px" />
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Meter">
                        <ItemTemplate>
                            <asp:Label ID="lblMeter" runat="server" Text='<%# Eval("Meter") %>'></asp:Label>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Label ID="MeterTotal" runat="server"></asp:Label>
                        </FooterTemplate>
                        <ItemStyle Width="150px" />
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Avg" >
                        <ItemTemplate>
                            <asp:Label ID="lblAvg" runat="server"></asp:Label>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Label ID="AvgTotal" runat="server"></asp:Label>
                        </FooterTemplate>                        
                        <ItemStyle Width="150px" />
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>

Code-behind.

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[5] { new DataColumn("Arrival Date"), new DataColumn("Name"), new DataColumn("Sale To"), new DataColumn("Pieces"), new DataColumn("Meter") });
                dt.Rows.Add("1-Oct-18", "Top Quality", "John", "10", "50");
                dt.Rows.Add("2-Oct-18", "Best M/S Quality", " ", "100", "600");
                dt.Rows.Add("3-Oct-18", "Low Quality", " ", "20", "105");
                dt.Rows.Add("3-Oct-18", "Best M/S Quality", " ", "1000", "5107");
                dt.Rows.Add("4-Oct-18", "Best M/S Quality", " ", "100", "940");
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                Label pieces = e.Row.FindControl("lblPieces") as Label;
                Label meter = e.Row.FindControl("lblMeter") as Label;
                Label avg = e.Row.FindControl("lblAvg") as Label;
                float avgvalue = float.Parse(meter.Text) / float.Parse(pieces.Text);
                avg.Text = avgvalue.ToString();
            }
        }

        protected void GridView1_DataBound(object sender, EventArgs e)
        {
            int piecesAmount = 0;
            int meterAmount = 0;
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                string pieces = ((Label)GridView1.Rows[i].FindControl("lblPieces")).Text;
                string meter = ((Label)GridView1.Rows[i].FindControl("lblMeter")).Text;
                piecesAmount += Convert.ToInt32(pieces);
                meterAmount += Convert.ToInt32(meter);
            }
            Label piecesTotal=(Label)GridView1.FooterRow.FindControl("PiecesTotal");
            Label meterTotal = (Label)GridView1.FooterRow.FindControl("MeterTotal");
            piecesTotal.Text = piecesAmount.ToString();
            meterTotal.Text = meterAmount.ToString();
            Label avgTotal = (Label)GridView1.FooterRow.FindControl("AvgTotal");
            float avgtotal = float.Parse(meterTotal.Text) / float.Parse(piecesTotal.Text);
            avgTotal.Text = avgtotal.ToString();
            GridView1.FooterRow.Cells[1].Text = "Grand Total";

        }

result:

Best Regards,

Jenifer

Answer 3

User-807418713 posted

Hello Jenifer

Thanks For Your Reply

But i Want to Show Avg Grand Total For Only  which have M/S  record Not All For All Data

Please need code

Answer 4

User753101303 posted

With the code above you could add something such as :

string name = ((Label)GridView1.Rows[i].FindControl("lblName")).Text;
if(name.Contains("M/S")) {
// sum
}

Answer 5

User839733648 posted

Hi Gopi.MCA,

Just as PatriceSc has said, you could add .Contains() method in DataBound to check if Name contains M/S.

        protected void GridView1_DataBound(object sender, EventArgs e)
        {
            int piecesAmount = 0;
            int meterAmount = 0;
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                string pieces = ((Label)GridView1.Rows[i].FindControl("lblPieces")).Text;
                string meter = ((Label)GridView1.Rows[i].FindControl("lblMeter")).Text;
                string name = GridView1.Rows[i].Cells[1].Text;
                if (name.Contains("M/S"))
                {
                    piecesAmount += Convert.ToInt32(pieces);
                    meterAmount += Convert.ToInt32(meter);
                }
            }
            Label piecesTotal = (Label)GridView1.FooterRow.FindControl("PiecesTotal");
            Label meterTotal = (Label)GridView1.FooterRow.FindControl("MeterTotal");
            piecesTotal.Text = piecesAmount.ToString();
            meterTotal.Text = meterAmount.ToString();
            Label avgTotal = (Label)GridView1.FooterRow.FindControl("AvgTotal");
            float avgtotal = float.Parse(meterTotal.Text) / float.Parse(piecesTotal.Text);
            avgTotal.Text = avgtotal.ToString();
            GridView1.FooterRow.Cells[1].Text = "Grand Total";
        }

reslut:

Best Regards,

Jenifer