Margin Calculations within Pivot Tables

Pivot Tables offer increased runtime functionality for end-users without requiring heavy customization by developers. While Pivot Tables are easy to use, one common question received is “How can I add runtime calculations to my subtotal and grand total levels?”

To do so, begin by creating your Pivot Table as normal, being sure to select as many sequence keys and record selections as needed. Below is a sample Pivot Table:

In the example above, we want to see what percent of total sales was spent on cost of goods sold. To accomplish this, navigate to calculations and add a new calculation, as shown below:

What is important to note is that the Calc Type is set to “Application”. Selecting application as the type within Pivot Tables will create a margin calculation automatically at runtime. After adding this calculation, be sure to compile/recompile. Now, at runtime, the margin calc can be selected and displayed, just like all other non-sequence key, displayed numeric fields:

Created: August 20, 2012 | Modified: June 12, 2017