Displaying average value in total column using PivotTable.js

Question

I am using pivotable.js library, in this pivotable.js I want show average values of all cells in the total column instead of total of all cell values. Please have a look at the image below, which show how I want to display values in in pivottable.js

var average = $.pivotUtilities.aggregatorTemplates.average;
var numberFormat = $.pivotUtilities.numberFormat;
var intFormat = numberFormat({digitsAfterDecimal: 1});

$("#output").pivot(
  [
    {color: "green", shape: "null", value: 0},
    {color: "blue", shape: "circle", value: 1},
    {color: "red", shape: "triangle", value: 2},
    {color: "red", shape: "triangle", value: 12},
    {color: "blue", shape: "circle", value: 3},
    {color: "blue", shape: "triangle", value: 12}
  ],
  {
    rows: ["color"],
    cols: ["shape"],
    aggregator: average(intFormat)(["value"])
  }
);

Could you please let me know how can I achieve this. Here is fiddle

https://jsfiddle.net/dky0hh1y/5/

My custom aggregator

 var successRate = function() {
  return function() {
    return {
      sumSuccesses: 0,
      sumTrials: 0,
      push: function(record) {
        if (!isNaN(parseFloat(record.student))) {
          this.sumSuccesses += parseFloat(record.student);
        }

      },
      value: function() { return this.sumSuccesses },
      format: function(x) { return x; },
      numInputs: 0
    };
  };
};

Thanks

Answer 1

PivoTable.js seems to apply the same calculation method to all cells, including both row and column totals. If you need custom calculation totals, allow PivoTable.js to generate the pivot after the pvtTable element is ready. Then, iterate through all rows and cells as shown below to generate the desired total values:

// Set the output tag visibility to hidden in the HTML body.
//<div id="output" style="visibility:hidden;"></div>
$(document).ready(function () {
          const jsonData = getjsonData();
          $("#output").pivotUI(jsonData, { // pivotUI configuration...
   
           });


function formatTotalValue(aTotalValue, aCount) {
    // If there is no other calculation needed, simply return aTotalValue.
    return aTotalValue;

    // For calculating the average value.
    // Uncomment the next line if you want to enable this calculation.
    // return aTotalValue / aCount;
  }

  
$(".pvtTable").ready(function () {
    document.querySelector("th[class='pvtTotalLabel pvtColTotalLabel']").innerText = "Column Total Label";
    document.querySelector("th[class='pvtTotalLabel pvtRowTotalLabel']").innerText = "Row Total Label";
    var pvtTable = document.querySelector("table[class='pvtTable']");

    var firstDataRowIndex = 2; // Ignoring column names
    var totalRowindex = pvtTable.rows.length - 1;

    var firstDataCellIndex = 1; // Ignoring row names
    var totalCellindex = pvtTable.rows[totalRowindex].cells.length - 1;

    // Initialize column total values
    for (var i = firstDataRowIndex; i <= totalRowindex; i++)
        pvtTable.rows[i].cells[totalCellindex].innerText = "";

    // Initialize row total values
    for (var j = firstDataCellIndex; j < totalCellindex; j++)
        pvtTable.rows[totalRowindex].cells[j].innerText = "";

    // Calculate all total values
    for (var i = firstDataRowIndex; i < totalRowindex; i++) {
        for (var j = firstDataCellIndex; j < totalCellindex; j++) {
            cellValue = pvtTable.rows[i].cells[j].innerText;
            pvtTable.rows[i].cells[totalCellindex].innerText += cellValue;
            pvtTable.rows[totalRowindex].cells[j].innerText += cellValue;
            pvtTable.rows[totalRowindex].cells[totalCellindex].innerText += cellValue;
        }
    }

    // Format the calculated total values
    for (var i = firstDataRowIndex; i < totalRowindex; i++)
        pvtTable.rows[i].cells[totalCellindex].innerText = FormatTotalValue(pvtTable.rows[i].cells[totalCellindex].innerText, totalCellindex - firstDataCellIndex);

    for (var j = firstDataCellIndex; j < totalCellindex; j++)
        pvtTable.rows[totalRowindex].cells[j].innerText = FormatTotalValue(pvtTable.rows[totalRowindex].cells[j].innerText, totalRowindex - firstDataRowIndex);

    pvtTable.rows[totalRowindex].cells[totalCellindex].innerText = FormatTotalValue(pvtTable.rows[totalRowindex].cells[totalCellindex].innerText, (totalRowindex - firstDataRowIndex) * (totalCellindex - firstDataCellIndex));
    // ***

    // After all calculation is done, set output tag visibility = visible
    $("#output").css("visibility", "visible");
});

Answer 2

PivoTable.js uses the same aggregator function for all cells, total or not. If you want to have a different method of computing values in the total row/col you'll need to write your own custom aggregator. Here is the documentation for this https://github.com/nicolaskruchten/pivottable/wiki/Aggregators