ExcelScript.ShowAsCalculation enum

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.