AdapTable Version:

Framework:

Using Weighted Averages

Summary

AdapTable offers 2 ways to provide Weighted Averages:
- through Weighted Average Aggregations in a Layout (referencing the Weighted Column)
- via the AVG AdapTableQL Expression Function which takes an optional WEIGHT parameter
Additionally Weighted Average aggregations can be viewed in Cell Summary Operations

Weighted Averages are commonly used in Finance and other sectors.

They provide a more 'accurate' average of a set of data than simply using the arithmetic mean.

Weighted average is a calculation that takes into account the varying degrees of importance of the numbers in a data set. In calculating a weighted average, each number in the data set is multiplied by a predetermined weight before the final calculation is made.

Hint

Weighted Averages are commonly used in statistical analysis, stock portfolios and teacher grading averages

AdapTable provides 3 different ways of viewing Weighted Averages:

Creating a Weighted Average Aggregation in a Layout
Seeing the Weighted Average Summary in the Cell Summary or Row Summary
Providing a Weighted Average Calculation in an AdapTableQL Expression

Weighted Average Aggregations

In AdapTable Columns can be defined as having Weighted Average Aggregations.

These definitions can be provided by both developers at design-time and end users at run-time.

Each Layout in AdapTable contains a TableAggregationColumns (or PivotAggregationColumns) property.

This defines which Columns to aggregate, and which operation to use, when the Grid is Row-Grouped.

Find Out More

Guides for Defining Table Layouts and Pivot Layouts explain in detail how to provide Aggregations

The TableAggregationColumns (or PivotAggregationColumns) property can also be of type WeightedAverageAggregation.

Note

Adaptable provides a custom aggFunc of weightedAverage which is added to the aggFuncs supplied by AG Grid

This object is a record where the key is the name of the Column which will be Aggregated, and a weightedColumnId property supplies the name of the column providing the Weight.

Hint

There is also a type property which should always be set to 'weightedAverage'

So imagine a scenario where you have a Grid with an examResult column but you want to provide a weighted average using another column, attendance in the calculation.

The Initial Adaptable State to create the Weighted Average would be:

TableAggregationColumns: [
  {
    ColumnId: 'examResult',
    AggFunc: {
      type: 'weightedAverage',
      weightedColumnId: 'attendance',
    },
  },
],

Note

It is also possible to create a Weighted Average aggregation using the Table Layout Wizard
When weightedAvg is the selected aggFunc, AdapTable will display a second dropdown for the Weighted Column

Filtering

The Weighted Average Aggregation evaluates using only the currently filtered rows in the Grid.

This follows the AG Grid pattern where aggregations take filters into account by default unless explicity configured otherwise via the suppressAggFilteredOnly property in Grid Options.

Hint

Set suppressAggFilteredOnly to true in Grid Options for Weighted Average aggregations to include non-filtered rows

Weighted Average Summaries

AdapTable is able to display the Weighted Average for any selected Cells or Rows:

For Cell Summary - select weightedAvg in the Cell Summary Toolbars, Tool Panel or Status Panel
For Row Summary - add to the RowSummaries property in the Layout

Caution

Cell and Row Summaries require the summarised Column to have an associated Weighted Average Aggregation
This should be provided in the Layout's Aggregations (see above)

Weighted Averages in Expressions

Weighted Averages can also provided in AdapTableQL by using Aggregation Expressions.

Hint

This is particularly useful if you wish to show Weighted Averages in a Calculated Column

It is achieved by providing a WEIGHT parameter to the AVG Expression Function.

Note

The WEIGHT Expression Function recieves a single argument which is the name of the Column to use for weighting

 // Return Weighted Average for 'examResult' Column (using 'attendance' column for weighting)
 AVG([examResult], WEIGHT([attendance]))

The Weighting can also be grouped if necessary by using the GROUP_BY Expression Function:

 // Weighted Average for 'examResult' (with 'attendance' for weighting) grouping by Class & Pupil
 AVG([examResult], WEIGHT([attendance]), GROUP_BY([class], [pupil]))

Using Weighted Averages

Fork Fork

This example illustrates the different ways that Weighted Averages can be used in AdapTable:
- Aggregation - the Exam Result Column has been given a Weighted Average Aggregation, using the Attendance Column as the weighted column
- Expression Functions: - the Calc Column Calculated Column - has an Aggregation Expression (which includes the AVG, WEIGHTED & GROUP_BY functions)
- Cell Summary - a Cell Summary has been set to be Weighted Avg (and some cells have been highlighted for convenience)
- Row Summary - a Row Summary has been added to the top of the Grid showing Weighted Average for Exam Result Column
Note: the Calculated Column provides the same result as the Weighted Average Aggregation and Selected Cells Summary

Expand to see how these 3 Weighted Average elements were set up

The Weighted Average Aggregation for the Exam Result Column was provided in Layout Initial State

Layout: {
  CurrentLayout: 'Full Layout',
  Layouts: [
    {
      Name: 'Full Layout',
      TableColumns: ['pupil', 'subject','examResult', 'attendance', 'class', 'Calc'],
      RowGroupedColumns: ['class', 'pupil'],
      SuppressAggFuncInHeader: true,
      TableAggregationColumns: [
      {
        ColumnId: 'examResult',
        AggFunc: {
          type: 'weightedAverage',
          weightedColumnId: 'attendance',
        },
      }],
    },
  ],
},

The Cell Summary Operation was set to Weighted Avg, row groups opened and cells highlighted using Adaptable API methods:

adaptableApi.cellSummaryApi.setCurrentCellSummaryOperation('Weighted Avg');
adaptableApi.gridApi.expandAllRowGroups();
setTimeout(() => {
  const gridCellRange: GridCellRange = {
    columnIds: ['examResult'],
    rowIndexStart: 2,
    rowIndexEnd: 4,
  };
  adaptableApi.gridApi.selectCellRange(gridCellRange);
}, 100);

The Calc Column Calculated Column was defined in Calculated Column Initial State:

CalculatedColumn: {
  CalculatedColumns: [
  {
    ColumnId: 'calcColumn',
    FriendlyName: 'Calc Column',
    Query: {
      AggregatedScalarExpression: 'AVG([examResult], WEIGHT([attendance] ), GROUP_BY([class], [pupil]) ) ',
    },
    CalculatedColumnSettings: {
      DataType: 'number',
    },
  },
]},

Try It Out

Select the 5 cells for Andy in the Exam Result column and not how all the Aggregations add up
Type 2 in the filter for the Attendance column (to filter the Grid) and not how the Exam Result aggregations automatically change

import {AdaptableOptions} from '@adaptabletools/adaptable';

export const adaptableOptions: AdaptableOptions = {
  primaryKey: 'Id',
  adaptableId: 'Weighted Average',
  initialState: {
    Dashboard: {
      Tabs: [
        {
          Name: 'Main',
          Toolbars: ['CellSummary', 'Layout'],
        },
      ],
      ModuleButtons: ['CalculatedColumn'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['CellSummary', 'Layout'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'calcColumn',
          FriendlyName: 'Calc Column',
          Query: {
            AggregatedScalarExpression:
              'AVG([examResult], WEIGHT([attendance] ), GROUP_BY([class], [pupil]) ) ',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Full Layout',
      Layouts: [
        {
          Name: 'Full Layout',
          TableColumns: [
            'pupil',
            'subject',
            'examResult',
            'attendance',
            'class',
            'calcColumn',
          ],
          RowGroupedColumns: ['class', 'pupil'],
          RowSummaries: [
            {
              Position: 'Top',
              ColumnsMap: {
                examResult: 'WEIGHTED_AVERAGE',
              },
            },
          ],
          SuppressAggFuncInHeader: true,
          TableAggregationColumns: [
            {
              ColumnId: 'examResult',
              AggFunc: {
                type: 'weightedAverage',
                weightedColumnId: 'attendance',
              },
            },
            {
              ColumnId: 'blah',
              AggFunc: true,
            },
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-examResult',
          Scope: {
            ColumnIds: ['examResult', 'calcColumn'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
      ],
    },
  },
};

Weighted Averages

(Recorded with AdapTable v15.0)

FAQ

What happened to Weighted Averages in Finance Plugin? These were removed in Version 12 and replaced by the superior options offered here

Why does the Weighted Average Aggregtion evaluate all rows, and not just filtered ones? This will be fixed in a future patch, and AG Grid's suppressAggFilteredOnly property will be used when deciding which rows should be included in the evaluation

PreviousGrand Total Rows NextOnly Aggregation

Framework: