Edit

Share via

ExcelScript.ShowAsCalculation enum

  • Reference
Package:
ExcelScript

The ShowAs calculation function for the DataPivotField.

Remarks

Examples

/**
 * The script changes the display for "Crates Sold at Farm".
 * It shows the percentage of the grand total, 
 * instead of the default sum.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable named "Farm Pivot".
  const farmPivot = workbook.getPivotTable("Farm Pivot");

  // Get the data hierarchy "Sum of Crates Sold at Farm".
  const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at Farm");

  // Show the data as a percentage of the grand total.
  farmSales.setShowAs({
    calculation: ExcelScript.ShowAsCalculation.percentOfGrandTotal
  });
}

Fields

differenceFrom

Difference from the specified Base field and Base item.
index

Calculates the values as follows: ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))
none

No calculation is applied.
percentDifferenceFrom

Difference from the specified Base field and Base item.
percentOf

Percent of the specified Base field and Base item.
percentOfColumnTotal

Percent of the column total.
percentOfGrandTotal

Percent of the grand total.
percentOfParentColumnTotal

Percent of the column total for the specified Base field.
percentOfParentRowTotal

Percent of the row total for the specified Base field.
percentOfParentTotal

Percent of the grand total for the specified Base field.
percentOfRowTotal

Percent of the row total.
percentRunningTotal

Percent running total of the specified Base field.
rankAscending

Ascending rank of the specified Base field.
rankDecending

Descending rank of the specified Base field.
runningTotal

Running total of the specified Base field.
unknown

Calculation is unknown or unsupported.