none
Calculate average of a column in a group

    질문

  • I am using SPOnline so my options are JSLink/CSR. do you have sample codes?

    OR any other approach - reporting/power bi

    I have a list that contains details of Cars. The view was grouped by Brand.

    I want to compute for the average of the columns. please see highlighted items in red in the attached image


    2016년 9월 2일 금요일 오전 7:11

답변

  • Thanks for the solution but it doesn't seem to work.

    Anyway, I just used the OOTB Average option in the view.

    • 답변으로 표시됨 Ton-Ton Keaton 2016년 9월 5일 월요일 오전 4:42
    2016년 9월 5일 월요일 오전 4:42
  • Hi,

    We can use jQuery to achieve it.

    <script src="//code.jquery.com/jquery-3.1.0.min.js" type="text/javascript"></script>
    <script type="text/javascript">
    var myVar;
    $(function () {    	    
        myVar = setInterval(setAverage, 100);
    });
    function setAverage(){     	 
    	$(".ms-listviewtable tbody[id^='tbod']").each(function(){
    		if($(this).children("tr").text()!="Working on it..."){
    			var itemCount=$(this).children("tr").length;
    			var sum1=0;
    			var sum2=0;
    			if(itemCount>0){
    				if($(this).children("tr").text().indexOf("Average")<0){
    					$(this).children("tr").each(function(){
    						sum1+=parseInt($(this).children("td").eq(5).text());
    						sum2+=parseInt($(this).children("td").eq(6).text());
    					});
    					$(this).append("<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>Average:</td><td class='ms-cellstyle ms-vb2'><div align='right' class='ms-number'>"+Math.ceil(sum1/itemCount)+"</div></td><td class='ms-cellstyle ms-vb2'><div align='right' class='ms-number'>"+Math.ceil(sum2/itemCount)+"</div></td></tr>");
    				}
    			}
    		}
    	});	
    }
    </script>

    Best Regards,

    Dennis


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • 답변으로 표시됨 Ton-Ton Keaton 2016년 9월 6일 화요일 오전 1:18
    2016년 9월 5일 월요일 오전 8:31
    중재자

모든 응답

  • Hi,

    I am not sure whether it can be achieved using OOTB feature , however you can achieve it by adding Data view web part using Sharepoint designer. Below link shows the way to add SUM of column values. For Average calculations , you can have the formulae like SUM/COUNT of items

    https://chanakyajayabalan.wordpress.com/2010/07/01/summing-calculated-columns-in-group-by-in-sharepoint-list-dataview-webpart/

    Regards,

    Amit 

    2016년 9월 2일 금요일 오전 10:52
  • Thanks for the solution but it doesn't seem to work.

    Anyway, I just used the OOTB Average option in the view.

    • 답변으로 표시됨 Ton-Ton Keaton 2016년 9월 5일 월요일 오전 4:42
    2016년 9월 5일 월요일 오전 4:42
  • Hi,

    We can use jQuery to achieve it.

    <script src="//code.jquery.com/jquery-3.1.0.min.js" type="text/javascript"></script>
    <script type="text/javascript">
    var myVar;
    $(function () {    	    
        myVar = setInterval(setAverage, 100);
    });
    function setAverage(){     	 
    	$(".ms-listviewtable tbody[id^='tbod']").each(function(){
    		if($(this).children("tr").text()!="Working on it..."){
    			var itemCount=$(this).children("tr").length;
    			var sum1=0;
    			var sum2=0;
    			if(itemCount>0){
    				if($(this).children("tr").text().indexOf("Average")<0){
    					$(this).children("tr").each(function(){
    						sum1+=parseInt($(this).children("td").eq(5).text());
    						sum2+=parseInt($(this).children("td").eq(6).text());
    					});
    					$(this).append("<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>Average:</td><td class='ms-cellstyle ms-vb2'><div align='right' class='ms-number'>"+Math.ceil(sum1/itemCount)+"</div></td><td class='ms-cellstyle ms-vb2'><div align='right' class='ms-number'>"+Math.ceil(sum2/itemCount)+"</div></td></tr>");
    				}
    			}
    		}
    	});	
    }
    </script>

    Best Regards,

    Dennis


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • 답변으로 표시됨 Ton-Ton Keaton 2016년 9월 6일 화요일 오전 1:18
    2016년 9월 5일 월요일 오전 8:31
    중재자
  • Thanks for this Dennis, at least I have two options on how to implement this. I just modified the code a little bit to make it compatible with my live list.

    Thanks!

    2016년 9월 6일 화요일 오전 1:18
  • 

    Hi Dennis,

    I really like your jquery and had executed into my SharePoint List and it calculated two of 5 columns that I had. (I can't insert an image on here because my account is not verified, not sure how to change this). I have in this order a Overall Average column, Customer Service Rating column, Budget Adherence column, Schedule Aherence column, and a Scope Adherence column. When I executed your jquery, it calculated the average for the budget and the schedule column. I attempted to modify the code but was unsuccessful, can you help? Also how can I move the average to be on top of the grouping instead of the bottom of the grouping?

    2018년 6월 14일 목요일 오후 2:28